Unpivoting multiple column groups

[wilson_smyth]

Hi.

I have a flat file where each row contains multiple column groups of data.
I need to get a row per group. I though unpivot functionality was the way to accomplish this but i have not been successful so far.

Below are examples of the source data and the final format required. Would appreciate any advice about how to tackle this.

ID	Date	Desc	Amount	SplitAmount1_Desc	SplitAmount1	SplitAmount2_Desc	SplitAmount2	Reserve1_Desc	Reserve1Amount
1	01/01/2020	Donation	1000	fund A	550	Fund B	400	Reserved for contingency	50
2	06/06/2021	Donation	400	Fund J	200			Additional Costs	200

How i need it to look is as follows:

ID	Date	Amount	SplitAmount	Desc
1	01/01/2020	1000	550	Fund A
1	01/01/2020	1000	400	Fund B
1	01/01/2020	1000	50	Reserved for Contingency
2	06/06/2021	400	200	Fund J
2	06/06/2021	400	200	Additional Costs

[AlexisOlson · Accepted solution]

Here's how I'd do it with pivoting and unpivoting:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY2xCsMwDER/RWgORDZJ97SlQ8esxkOInWIoMsRuoX9fyc1SkOCedNw5hwY7JNPLWLIkcM281JRZpCHSy/biAJOIcVS8KZ5FDO07xxL3dwyw5R3WzDXxI/L6UT+h7xxarTj1MlJh/it+GS3yLsI2hGOnEJL6lidccqnlMHj/BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Desc = _t, Amount = _t, SplitAmount1_Desc = _t, SplitAmount1 = _t, SplitAmount2_Desc = _t, SplitAmount2 = _t, Reserve1_Desc = _t, Reserve1Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Desc", type text}, {"Amount", Int64.Type}, {"SplitAmount1_Desc", type text}, {"SplitAmount1", Int64.Type}, {"SplitAmount2_Desc", type text}, {"SplitAmount2", Int64.Type}, {"Reserve1_Desc", type text}, {"Reserve1Amount", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Reserve1_Desc", "Reserve1Amount_Desc"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Amount", "Desc", "Date", "ID"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Label", "PivotColumn"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter",null,"SplitAmount",Replacer.ReplaceValue,{"PivotColumn"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Desc"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[PivotColumn]), "PivotColumn", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"ID", "Date", "Amount", "SplitAmount", "Label", "Desc"})
in
    #"Reordered Columns"

You can also do it without pivoting and unpivoting like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY2xCsMwDER/RWgORDZJ97SlQ8esxkOInWIoMsRuoX9fyc1SkOCedNw5hwY7JNPLWLIkcM281JRZpCHSy/biAJOIcVS8KZ5FDO07xxL3dwyw5R3WzDXxI/L6UT+h7xxarTj1MlJh/it+GS3yLsI2hGOnEJL6lidccqnlMHj/BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Desc = _t, Amount = _t, SplitAmount1_Desc = _t, SplitAmount1 = _t, SplitAmount2_Desc = _t, SplitAmount2 = _t, Reserve1_Desc = _t, Reserve1Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Desc", type text}, {"Amount", Int64.Type}, {"SplitAmount1_Desc", type text}, {"SplitAmount1", Int64.Type}, {"SplitAmount2_Desc", type text}, {"SplitAmount2", Int64.Type}, {"Reserve1_Desc", type text}, {"Reserve1Amount", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"ID", "Date", "Amount", "SplitAmount1_Desc", "SplitAmount1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"SplitAmount1_Desc", "Desc"}, {"SplitAmount1", "SplitAmount"}}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Changed Type",{"ID", "Date", "Amount", "SplitAmount2_Desc", "SplitAmount2"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Other Columns1",{{"SplitAmount2_Desc", "Desc"}, {"SplitAmount2", "SplitAmount"}}),
    #"Removed Other Columns2" = Table.SelectColumns(#"Changed Type",{"ID", "Date", "Amount", "Reserve1_Desc", "Reserve1Amount"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Other Columns2",{{"Reserve1_Desc", "Desc"}, {"Reserve1Amount", "SplitAmount"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns", #"Renamed Columns1", #"Renamed Columns2"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"ID", Order.Ascending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([SplitAmount] <> null))
in
    #"Filtered Rows"

[jbwtp]

I like the non-pivoting version, but may be generalised it as below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY2xCsMwDER/RWgORDZJ97SlQ8esxkOInWIoMsRuoX9fyc1SkOCedNw5hwY7JNPLWLIkcM281JRZpCHSy/biAJOIcVS8KZ5FDO07xxL3dwyw5R3WzDXxI/L6UT+h7xxarTj1MlJh/it+GS3yLsI2hGOnEJL6lidccqnlMHj/BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Desc = _t, Amount = _t, SplitAmount1_Desc = _t, SplitAmount1 = _t, SplitAmount2_Desc = _t, SplitAmount2 = _t, Reserve1_Desc = _t, Reserve1Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Desc", type text}, {"Amount", Int64.Type}, {"SplitAmount1_Desc", type text}, {"SplitAmount1", Int64.Type}, {"SplitAmount2_Desc", type text}, {"SplitAmount2", Int64.Type}, {"Reserve1_Desc", type text}, {"Reserve1Amount", Int64.Type}}),
    
    Rules = {
                {"Desc",                "SplitAmount"},
                {"SplitAmount1_Desc",   "SplitAmount1"},
                {"SplitAmount2_Desc",   "SplitAmount2"},
                {"Reserve1_Desc",       "Reserve1Amount"}
            },
    Permanent = List.RemoveItems(Table.ColumnNames(#"Changed Type"), List.Combine(Rules)),
    
    f = (cols as list)=>
        let 
            #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",Permanent & cols),
            #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",List.Zip({cols} & {Rules{0}}))
        in #"Renamed Columns",
    
    Process = List.Accumulate(List.Skip(Rules), #table({},{}), (a,n)=> Table.Combine({a, f(n)})),
    #"Sorted Rows" = Table.Sort(Process,{{"ID", Order.Ascending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([SplitAmount] <> null))
in
    #"Filtered Rows"