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
Super User
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.
- lbendlin
The only "book" you will ever need on M is Ben Gribaudo's Primer. It is absolutely fantastic.