Calculate Sales Commission for Weekdays & Weekends using Power Query M Code

Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to calculate Sales Commission for Weekdays and Weekends using Power Query M Code. Let’s get started

Video

Sample Data

In the caption below, we have Sales Data that is officially formatted as an Excel Table and fSalesData has be given as Table name

Load Data to Power Query

Next, we want to load the dataset to the Power Query editor. To do that:

• Click Inside the Dataset
• In the Get & Transform Data group of the Data tab, click on From within sheet (or from Table/Range depending on the version of Excel you’re using)

From the caption below, we can see that the dataset has been loaded to the Power Query editor

Power Query M Function

Our goal is to multiply Weekdays Commission Rate of 6% by the Total for all the Transactions that falls within the weekday (Mondays to Fridays). On the other hand, we are going to multiply the Weekends Commission Rate of 10% by the Total for all the transactions that falls within the weekend (Saturdays and Sundays). To achieve this, we are going to use Power Query M Function called Date.DayOfWeek Function

Date.DayOfWeek Function

Returns a number (from 0 to 6) indicating the day of the week of the provided dateTime.

• dateTime: A date, datetime, or datetimezone value.
• firstDayOfWeek: A Day value indicating which day should be considered the first day of the week. Allowed values are Day.Sunday, Day.Monday, Day.Tuesday, Day.Wednesday, Day.Thursday, Day.Friday, or Day.Saturday. If unspecified, a culture-dependent default is used.

To write the M Code, In the General group of the Add Column tab, click on Custom Column

• In the Custom Column dialogue box, provide Commission as the columns
• In the Custom Column Formula, execute the M Code below

if Date.DayOfWeek([Date],Day.Monday) >=6 then [Total]*0.10 else [Total]*0.06

The above M code uses IF Function which is native to Power Query language. This is different from the native Excel worksheet IF function

• Click OK

From the caption below, we can see the nearly added column with the Commission calculation for the weekdays and weekends Sales Report

Load Solution to Excel

Finally, we want to load the solution to Excel. To do that:

• In the Home tab, click on Close & Load To
• In the Import wizard, select Table and destination cell in the existing Worksheet
• Click OK

There we have the final result in the worksheet as seen below