excelpowerquerymexcel-365

How to add a missing column to the Transform Sample File

Background

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...

Problem

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.

What I have tried

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

enter image description here

however when I select that step, it shows this error:

enter image description here

Question

How do I fix my IF statement so that it adds a column named "LOS" if it is not already there.

Update

My list of applied steps is as follows and the Green step is where I added the IF statement

enter image description here

Solution

  • 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.