Unpivoting Group of Columns by start of columnnames

[hwoehrle]

Hi,

i'm trying to unpivot a table that has some grouped columns:

The goal woud be to have the blue datasetunpivoted to the green.

Here is the sample data:

Since the columnname-endings may change, a flexilble solution like "group all columns with name S1* and unpivot them..." would be fantastic 🙂

Thanks for any advice and hint!

Best regards,

Heiko

[Anonymous · Accepted solution]

try this

let
    Source = Excel.Workbook(File.Contents("C:\Users\sprmn\OneDrive\Documents\Power BI Desktop\SplitColumns.xlsx"), null, true),
    Org_Table = Source{[Item="Org",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Org_Table,{{"Basis", type text}, {"Description", type text}, {"S1.Typ", type text}, {"S1.H.UUID", type text}, {"S1.n", Int64.Type}, {"S1.E.UUID", type text}, {"S2.Typ", type text}, {"S2.H.UUID", type text}, {"S2.n", Int64.Type}, {"S2.E.UUID", type text}, {"S3.Typ", type text}, {"S3.H.UUID", type text}, {"S3.n", Int64.Type}, {"S3.E.UUID", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Basis", "Description"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, false), {"Part", "Attribute.2"}),
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Basis", "Description", "Part"}, {{"part", each Record.FromList([Value],[#"Attribute.2"])}}),
    #"Expanded part" = Table.ExpandRecordColumn(#"Grouped Rows", "part", {"Typ", "H.UUID", "n", "E.UUID"}, {"Typ", "H.UUID", "n", "E.UUID"})
in
    #"Expanded part"

I used the first "." as part separator S1, S2, etc.
If the real situation is different, you have to adapt to the logic

[KNP]

If the columns that you DON'T want to unpivot are constant, select those, right click and choose 'Unpivot Other Columns'