20 Power Query {M}agic Tips & Tricks?? - Part 2
We are now in part 2 of the series of learning 20 Power Query {M}agic Tips and Tricks for the Power BI Self-service developers.
If you want to follow along, I recommend you to go to part 1 here on how to connect to Microsoft's sample data:
Follow-a-long case with tips and tricks along the way
Today, I will cover tips 4 and 5 on a mental mapping of the Advanced Editor to DAX and how red text will break us and one of the greatest tips I learned the last year...
{4} let and in for M is like VAR and RETURN in DAX
"Every row you shape, every change you make. Every cell you break, every step you take, I'll be watching you" ?? Thank you for this wonderful quote, George Mount
In Power Query Formula Language {M} we have the Advanced Editor to see all the M script in one single document for a table.
If you have written any (advanced) DAX you would hace come across writing variables. For my mind it helped to think the VAR and RETURN in DAX to be like the let and in statement in M.
The cool difference in M is I can see the values/list/tables for each of the steps - I need to use DAX Debugger in Tabular Editor 3 to have something similar to that.
Something to be careful about is:
{5} Red text will break you
If you have worked with Power Query, you will quickly learn to hate these error messages.
What if I told you that we have other options that to edit manually names in the hardcoded M scripts?
Option 1: Manual effort
Manually remove the “Discount Band” from financials will break the dependent query Segment as the step removed other coumns have hardcoded the value of th column name (It would be the same if column name was renamed).
In order to fix this query is to manually remove the column name in the step as below:
Option 2: Use MissingField.Ignore
I came across a video or blog from Melissa de Korte that showed this awesome trick on how to handle errors like above.
Instead of removing the column name "Discount Band" manually from the code, we can add function MissingField.Ignore which will do this for us.
Option 3: Use MissingField.UseNull
It gets even better, because table schema changes can break your Power BI reports - so what about keeping the column but insert null values to highlight that something is going on here. I think this is one of the greatest tips I have seen in Power Query the past year ??
In general if you work with unstructured sources - you can change the Power BI Desktop Settings - have you?
If you work with unstructured sources as CSV and Excel - Power Query will be handling the data types for you. Connecting to SQL databases would inherit the data types from there - leading to fewer of these maintenance errors.
As with a best practice to deactivate Auto/date time in Power BI Desktop settings - you have right? :) We have an option to change default behavior for type detection for data types for unstructured sources.
If you want full control and avoid Change type steps randomly inserted for you - you should deactivate in Global and Current file settings.
???
If you want a guided tour - here's the video:
Credit and resources
Enjoy - and lookout for the upcoming newsletters for more tips and tricks ??
Thank you for reading my article, hope you enjoyed it. Comment or provide some input is much appreciated.
?? Hi my name is David Kofod Hanna, Senior Advisor and Consultant at twoday and my aim is to guide self-service Power BI developers for more enterprise managable concepts in a consumable and practical way - because I have been on that journey for years, and still is...