Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Add new column with IF-ELSE condition in Power Query

I have table as the one on the left below and would like to transform into the one on the right based on the following condition:   If MTH is 9,10 or 11, sum all the PRICE (SUM all PRICE for MTH 9,...
Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    4 years ago

    Hi Anonymous ,
    Here are the pics, might by in reverse order. 

    Groupby to get your sums, then divide by 12 to get your division, the use conditional col to decide which col to use.  Then delete intermediate columns.

    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel

Nathaniel_C's avatar
Nathaniel_C
Community Champion
4 years ago

Hi Anonymous Try this, paste this into your advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WslDSUTJUitWBsIzALEsgyxjOMgGzDA2ATFME0wzCNAQyzRFMCwjTCMi0RDCBGmJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [M = _t, Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"M", Int64.Type}, {"Column1", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"M"}, {{"Sum", each List.Sum([Column1]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Divide", each [Sum]/12),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Divide", type number}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if [M] = 9 or  [M] = 10 or  [M] = 11 then [Sum] else if [M] = 8  or  [M] = 12 then [Divide] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Sum", "Divide"})
in
    #"Removed Columns"


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

 

Will follow up with pic

Nathaniel_C's avatar
Nathaniel_C
Community Champion
4 years ago

Hi Anonymous ,
Here are the pics, might by in reverse order. 

Groupby to get your sums, then divide by 12 to get your division, the use conditional col to decide which col to use.  Then delete intermediate columns.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    4 years ago

    Source table