Forum Discussion

Kana_K's avatar
Kana_K
New Member
3 years ago

If Statement - check if the table has a column

Hello,

 

I am a beginner in power query and need help.

 

I have a table that has 3 or 5 columns depending on the month. Columns 1-3 are always in the table. I would like to use the IF statement.

I would like to check if column 4 is present in the table. If yes then insert a new column ([Column2]+[Column4]) otherwise the new column contains the values from column2.

 

I have tried this but it does not work:

if Table.HasColumns(#"previous step", „Column4”) then (Table.AddColumn(#"previous step", "SUM", each [Column2] + [Column4], type number) else null) else Table.AddColumn(#"previous step ", "new column", each [Column2])

 

What is wrong with this query?

 

Thanks!

Need Help

2 Replies

  • tackytechtom's avatar
    tackytechtom
    Icon for Most Valuable Professional rankMost Valuable Professional
    3 years ago

    Hi Kana_K ,

     

    Here a way of doing this:

    Case A (including Column4):

     

    Before:

     

    After:

     

     

    Case B (Without Column4):

    Before:

     

    After:

     

     

    Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRMjQyBpKJBQU5qSCegYGBUqxOtFJKahqQa2JqBiSTEvOAEMgwAsvGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", Int64.Type}}),
    #"Added Column" = if Table.HasColumns(#"Changed Type", "Column4") then Table.AddColumn(#"Changed Type", "NewColumn", each [Column2] + [Column4]) else Table.AddColumn(#"Changed Type", "NewColumn", each [Column2])
in
    #"Added Column"

     

    Having quickly compared your code with mine, I reckon the "else null" could have created the hickup. Otherwise, I think our code follows a very similar pattern (except that you do the number casting directly in the code, which actually makes more sense to me 🙂 )

     

    Let me know if this helps!

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    = if Table.HasColumns(#"PrevStep","ColumnName") then #"PrevStep" else Table.AddColumn(#"ColumnName","ColumnName", each 0)