Skip to main content

Power BI

Needs Votes

Robust M-Code for PowerBI.Dataflow ()

Vote (12) Share
's profile image

on 08 Dec 2018 02:42:12

Generally when you click on a table when navigating in a relational database, M code similar to the following is generated:

=Source{[Table Name = "Calendar (Fiscal)"]}[Data]

This code says “Drill down from the Source table, in the Data column, on the row where Table Name = “Calendar (Fiscal)”. “

However, this is the M code that is generated when I click on a table:

=Source{0}[Data]

This code says “Drill down from the Source table, in the Data column, on row with Index = 0 (the first row of the table)

This code is problematic. As new tables are added to Dataflow, referencing the row number is not robust.

Referencing the Table Name is better, but still not very robust. Entities in Dataflows are easy to rename.

What would be amazing is if there was a unique key that was generated for each entity within a Dataflow. I have to imagine that there is, since the following unique keys are also generatedand available through the PowerBI.Dataflows () function:

• Workspace
• Dataflow

Being able to reference an entity by its static key would be bulletproof M code, but it’s not visible through the PowerBI.Dataflow () function.

I don’t know if this behavior is driven by the fact we have < 15 rows in the table to navigate, but the automatically generated M should be more robust than directly referencing a row index.