Have you ever had to deal with missing columns in your data?
Happens especially when you pivot over a column (go tall-to-wide). Most of the time, you end up with all possible values in your current dataset; consequently, all the columns you expect are in your table. But, when you don’t, all hell breaks loose.
If you have calculated columns further down the pipeline that depend on these columns, you will end up with refresh errors. If you have measures in your data model that depend on these columns, they will throw errors. And the peskiest problem of them all, if you have pivot tables and charts that depend on these columns, the fields will vanish after the refresh.
I increasingly found myself having to use Table.HasColumns before adding columns with Table.AddColumn to deal with this issue. And I did not like having to do that for multiple columns manually.
Inspired by the work of Chandeep Chhabra and Rick de Groot, I set out to write a custom-function that will check whether a Table has a list of columns and add a blank column if the column does not exist.
I have presented one of my first iterations here, in which I used List.Accumulate to iteratively check and add columns.
I found that this function also came in handy when I am parsing PDF files and multiple other occasions. An optimized version of this is a staple in every single Query that gets loaded to a sheet or to the data model. In projects where missing columns are expected, I pair this function with Table.SelectColumns to ensure my tables have all the columns the data model expects, in the right order.
I hope it helps you. And more importantly, I hope it inspires you to dive deeper in the world of M-Language.
Walk through:
Code:
let
func = (table as table, columns as list,optional blanks as any) as table =>
let
result = List.Accumulate(columns, table,
(state, current) =>
if
Table.HasColumns(state, current)
then
state
else
Table.AddColumn(state, current, each blanks, type any)
)
in
result
in
func
