I have a Power Query which combines multiple excel workbooks with the same table name in the same directory into a single table which I will then be using for a source data for a pivot table. Or at least I did...
The problem is that there was a revision to the named table for some of the files and a new column was added. Basically its a mix a data files that keep changing. I need to add that column to the files that are missing it.
I spent a good chunk of time searching with google and finally found a youtube video that I understand to an extent. It basically says to add an IF
statement that checks if the column exists. If it does just return the original table. Otherwise Add the column.
My M code is currently
let
Source = Excel.Workbook(Parameter1, null, true),
Forecast_Table_Table = Source{[Item="Forecast_Table",Kind="Table"]}[Data],
#"Added Custom1" = Table.AddColumn(Forecast_Table_Table, "Missing", each if Table.HasColumns(Navigation,"LOS") then Navigation else Table.Combine({Navigation, Table.FromRows({},{"LOS"})})),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Custom", each [Project No]&[Task No]&[Office]&[Staff]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] <> null and [Custom] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
#"Removed Columns"
This is where I tried to add the if statement like they did in the video
however when I select that step, it shows this error:
How do I fix my IF
statement so that it adds a column named "LOS" if it is not already there.
My list of applied steps is as follows and the Green step is where I added the IF
statement
Not sure what you are expecting for output but your problem is that although your Applied Step is Navigation
, that's not really the name of the previous step. (Ask MS why).
In M-Code, add this step instead: (assuming you want a column Named LOS
with empty contents)
#"Add LOS" = if Table.HasColumns(Forecast_Table_Table,{"LOS"}) then Forecast_Table_Table
else Table.Combine({Forecast_Table_Table,#table({"LOS"},{})})
If you want something else in that column, please provide an example.