Power Query Transform Data with Unique Columns

0
votes

Source Data Table.

Source Data Table

Desired Output

Desired Output

We want to Extract all the Unique values from Each Type Columns and Pivot the unique values as Column headers.

Somewhat similar to this but we have more then one columns to look up unique values. Power Query - Transpose unique values and get matching values in rows

Number of Type columns in the Source table can increase or decrease over time.

1
excel-formulapowerquery

1 Answers

2
votes

The code below is created via standard menu options. This video takes you through the results of each step.

let
    Source = SourceData,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Key"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Type", each if Text.Start([Attribute],4) = "Type" then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Type"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.StartsWith([Attribute], "Type")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Type]), "Type", "Value")
in
    #"Pivoted Column"