Power BI M Code and Data Flows - Moving away from Power BI Desktop
As Power BI moves away from Power BI desktop and towards the service side, its going to be important to make sure you develop your knowledge of M code (IMO). This means using m code for transforming data in the power service power query editor and not using DAX in power BI desktop to create columns as well as not using the power BI query editor at all (except for grabbing M Code from Native SQL queries to place in Power BI service data flow queries).
Below are two examples of using M code in power query when adding custom columns in Power Query. The first, adds a custom column 'DayofWeekName' and the second adds a custom column OrderDateWeekNum when use the [OrderDate] field from the FactInternetSales table of the adventure works 2019 database.
The code is added to Power Query in the Power Service on tables within dataflows (you can use it in PBI desktop, but we're trying to move away from that).
The tables within the data flows are then pulled into power bi, with no more work to do and as they are in data flows, they can be reused and taken advantage of by other developers.
They use the functions: Date.DayOfWeekName() and Date.DayOfWeek()
This example is used when creating a custom column to the FactInternetSales tables of the Microsoft Adventure WorksDW2019 database sample.
So comparing the 2 methods, you could create a column in Power BI desktop and enter the following DAX:
OrderWeekDay = WEEKDAY(FactInternetSales[OrderDate],2)
vs.
Date.DayOfWeek([OrderDate],0) in M Code created in the Power Query Editor (in Power BI service of Power BI Desktop).
Yes, the code is different, its a different language.
DAX stands for Data Analysis Expressions (DAX code)
M stands for Mashable (M code)
When you create M Code in Power BI, the code gets wrapped into whatever was there before it, so
Date.DayOfWeek([OrderDate],0)
becomes:
i.e. so initially a first column was added using the graphical buttons:
and then the custom column M code was injected into it manually
There are about 50 date M Code functions to choose for. There around 700 M code functions in all.
The date functions are below and all can be used in this fashion.
https://docs.microsoft.com/en-us/powerquery-m/date-functions
Comments
Post a Comment