CODEWITHSUNDEEP
excel-formulapowerquery
user204245
user204245

Reputation: 253

Power Query Transform Data with Unique Columns

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.

Upvotes: 0

Views: 514

Answers (1)

MarcelBeug
MarcelBeug

Reputation: 2987

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"

Upvotes: 2

Related Questions