none
Power Query Merge Columns with Conditions RRS feed

  • Question

  • Hi, I have the following situation in PowerQuery:

    I have a table something like this:

    Vendor | Product | BalanceM1 | BalanceM2 | BalanceM3 | BalanceMn

    Pepe | Sprite | Sprint10 | Sprint 11

    Jhon | Coke | Sprint 9

    Lud | Beer | Sprint 12 | Sprint 13 | Sprint14

    So I need a filtered table only with the most recent sprint for ex:

    Vendor | Product | MostSprintRecent

    Pepe | Sprite | Sprint 11

    Jhon | Coke | Sprint 9

    Lud | Beer | Sprint14

    Can you help me?

    Thursday, September 22, 2016 6:47 PM

Answers

  • One solution is to select all the 'Balance' columns and merge them into one column containing the list of values. To achieve this, merge with no separator first, then manually edit the generated code and replace the combiner function with 'each _'. Then filter these list items to retain only the last non null values: apply a transform step to another column, and again modify the code to point to "MergedBalances" column, then apply the custom transformation "each List.Last(List.Select(_, each _<>null))".

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor", type text}, {" Product ", type text}, {" BalanceM1 ", type text}, {" BalanceM2 ", type text}, {" BalanceM3 ", type text}, {" BalanceM4", type any}, {" BalanceM5", type any}, {" BalanceM6", type any}, {" BalanceM7", type any}, {" BalanceM8", type any}, {" BalanceM9", type any}, {" BalanceM10", type any}, {" BalanceM11", type any}, {" BalanceM12", type any}}),
        #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{" BalanceM4", type text}, {" BalanceM5", type text}, {" BalanceM6", type text}, {" BalanceM7", type text}, {" BalanceM8", type text}, {" BalanceM9", type text}, {" BalanceM10", type text}, {" BalanceM11", type text}, {" BalanceM12", type text}}, "en-GB"),{" BalanceM1 ", " BalanceM2 ", " BalanceM3 ", " BalanceM4", " BalanceM5", " BalanceM6", " BalanceM7", " BalanceM8", " BalanceM9", " BalanceM10", " BalanceM11", " BalanceM12"}, each _ ,"MergedBalances"),
        #"Lowercased Text" = Table.TransformColumns(#"Merged Columns",{{"MergedBalances", each List.Last(List.Select(_, each _<>null))}})
    in
        #"Lowercased Text"


    Thursday, September 22, 2016 7:23 PM

All replies

  • One solution is to select all the 'Balance' columns and merge them into one column containing the list of values. To achieve this, merge with no separator first, then manually edit the generated code and replace the combiner function with 'each _'. Then filter these list items to retain only the last non null values: apply a transform step to another column, and again modify the code to point to "MergedBalances" column, then apply the custom transformation "each List.Last(List.Select(_, each _<>null))".

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor", type text}, {" Product ", type text}, {" BalanceM1 ", type text}, {" BalanceM2 ", type text}, {" BalanceM3 ", type text}, {" BalanceM4", type any}, {" BalanceM5", type any}, {" BalanceM6", type any}, {" BalanceM7", type any}, {" BalanceM8", type any}, {" BalanceM9", type any}, {" BalanceM10", type any}, {" BalanceM11", type any}, {" BalanceM12", type any}}),
        #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{" BalanceM4", type text}, {" BalanceM5", type text}, {" BalanceM6", type text}, {" BalanceM7", type text}, {" BalanceM8", type text}, {" BalanceM9", type text}, {" BalanceM10", type text}, {" BalanceM11", type text}, {" BalanceM12", type text}}, "en-GB"),{" BalanceM1 ", " BalanceM2 ", " BalanceM3 ", " BalanceM4", " BalanceM5", " BalanceM6", " BalanceM7", " BalanceM8", " BalanceM9", " BalanceM10", " BalanceM11", " BalanceM12"}, each _ ,"MergedBalances"),
        #"Lowercased Text" = Table.TransformColumns(#"Merged Columns",{{"MergedBalances", each List.Last(List.Select(_, each _<>null))}})
    in
        #"Lowercased Text"


    Thursday, September 22, 2016 7:23 PM
  • Works great, thank u!!!
    Thursday, September 22, 2016 7:44 PM