Forum Discussion
M Code to remove blanks and nulls from rows in a single sweep
Here are all the transforms, including the changed types and column names. This replaces the entirety of your function (minus the removal of duplicates, but that's easy to add).
h/t to Mr. von Neumann
I'm still failing to understand.
In your code I see this line (truncated):
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
...
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [
#"Dept ID" = _t,
#"Supplier Name" = _t,
#"Supplier ID" = _t,
#"Line Description" = _t,
#"Sum of monetary amount" = _t,
Account = _t,
#"Account Description" = _t,
#"Business Unit" = _t,
#"PO Number" = _t,
#"Line Number" = _t,
#"Schedule Number" = _t,
...It appears the column names are still hardcoded?
If IT notifies me at 2:30pm that Finance requested to change the column name "Payment Date" to "Accrual Date", and by the way they've added a column "Accrued Amount" that I might be interested in, I can't modify the code that day to kick off a rerun since those fields aren't going to be in my raw data dump until 6am the following morning. I need my report available at 9am. If I get in at 8am and make the changes before kicking off the job it won't be available until 11am. Currently If I just make the changes to my Excel table by the time I leave the office then the job will run with the correct fields in the morning.
Every other activity in my function is able to run off the column name listing from the Excel table without hardcoding column names. Can M do the same for this requirement?
Super UserThat first Source line is just the sample data that you gave me (Power Query adds some compression and (wrong) meta data). In your real world you would replace that step with the pointer to the actual source table.
You can see that by examining the "applied steps" one by one.
The M code I provided is totally flexible, and running off the reference table instructions. But beware - Power Query does not take kindly to dynamic structural changes in the output (adding/removing/renaming columns) - You will make the acquaintance of the (very unpleasant) "Evaluating..." monster.
Thank you. Our organization is not a big Power BI user so most our PQ work is in Excel. An advantage for example is that I can take the data, find (say) users who created requisitions without ever making a corresponding purchase order and send targeted emails using VBA to get Outlook info and see if they want to cancel the req. MSFT will catch up with that at some point I suppose.
I'm unfamiliar with the "duplicate" construction you used - basically defining the action in the xp_ lines and then using Expression.Evaluate. Since I began working in PQ around 8 years ago and rarely go back to modify my code I'm sure the syntax and performance around your construction is superior, but it's new to me.
- lbendlin
I'm unfamiliar with the "duplicate" construction you used - basically defining the action in the xp_ lines and then using Expression.Evaluate.That is mainly for code readability and easier debugging. You can do that in a single step but it will be really hard to remember what it does. For example:
#"Removed Empty" = Expression.Evaluate("Table.SelectRows(Source, each not List.Contains({"""",null},[" & Text.Combine(Table.SelectRows(TransformReference, each ([Remove Empty] = "Y"))[Old Name],"]) and not List.Contains({"""",null},[") & "]))",[Table.SelectRows=Table.SelectRows,Source=Source,List.Contains=List.Contains]),I wouldn't wish that on my enemy, and certainly not on the developer after me. There's PowerQueryFormatter.com which makes it a bit more readable:
Expression.Evaluate( "Table.SelectRows(Source, each not List.Contains({"""",null},[" & Text.Combine( Table.SelectRows(TransformReference, each ([Remove Empty] = "Y"))[Old Name], "]) and not List.Contains({"""",null},[" ) & "]))", [Table.SelectRows = Table.SelectRows, Source = Source, List.Contains = List.Contains] )Is there a good M book to have, like Rob Collie's DAX book, to get a handle on M? I find it a very dense language... between treating something between a list and a record, square vs curly brackets, the "_" operator, a lot of power is tucked into small space.