Search code examples
excelpowerquery

Excel Power Query Combine Columns on new lines

I have a power query which currently just combines multiple sheets into one appended worksheet. Works fine.

My columns are:

[DATE][NAME1][AMOUNT1][NAME2][AMOUNT2]

What I'd like to do is display this information like this:

[DATE][NAME][AMOUNT]

So if Name1 and amount1 are Bill and $5 and Name2 and amount2 are Frank and $1 it would read:

1/1/24 Bill $5

1/1/24 Frank $1

and so on. I know I could write this all in VBA but trying to handle this without going that direction. Thoughts?

Solution

  • Using Power Query you could try the following to reach the desired results:

    enter image description here

    Add the following in the advanced editor of PQ:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"NAME1", type text}, {"AMOUNT1", type number}, {"NAME2", type text}, {"AMOUNT2", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"DATE"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute.1]), "Attribute.1", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"})
in
    #"Removed Columns"

    Updated M-Code:

    enter image description here

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"DATE"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Grouped Rows" = Table.Group(#"Split Column by Character Transition", {"DATE", "Attribute.1"}, {{"All", each _, type table [DATE=nullable date, Attribute.1=nullable text, Value=any]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"DATE", "Attribute.1", "Value", "Index"}, {"DATE", "Attribute.1", "Value", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Attribute.1]), "Attribute.1", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"DATE", type date}, {"NAME", type text}, {"AMOUNT", Currency.Type}})
in
    #"Changed Type"