Unpivot 2 level Cross Table

[joshua1990]

Hi all!

I would like to transform a specific cross table into a list.

This is the structure of the pivot table:

	Level 1	Level 2
Article	Attr. 1	Attr. 2	Attr. 3	Attr. 4	Attr. 1	Attr. 2	Attr. 3	Attr. 4
ABC	TRUE	FALSE	TRUE	TRUE	FALSE	TRUE	FALSE	TRUE
DEF	TRUE	TRUE	FALSE	TRUE	TRUE	FALSE	TRUE	FALSE
ERG	FALSE	TRUE	TRUE	FALSE	TRUE	TRUE	FALSE	TRUE
ETZ	TRUE	FALSE	TRUE	TRUE	FALSE	TRUE	TRUE	FALSE

And I would like to get this structure in the end:

Article	Level	Attribute	Value
ABC	Level 1	Attr. 1	TRUE
ABC	Level 1	Attr. 2	FALSE
ABC	Level 1	Attr. 3	TRUE
ABC	Level 1	Attr. 4	TRUE
ABC	Level 2	Attr. 1	FALSE
ABC	Level 2	Attr. 2	TRUE
ABC	Level 2	Attr. 3	FALSE
ABC	Level 2	Attr. 4	TRUE

How is this possible in Power Query?

[AlexisOlson]

The key steps here are Transpose, Promote Headers, and then unpivot all except the Level and Article columns.

Here's a full M query you can paste into the Advanced Editor of a new Blank query and examine each step in the Applied Steps pane:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJR8kktS81RMASy4AgiZIQQitWJVnIsKslMzkkFch1LSor0wDogLCM4yxjOMiFaHdhsJ2egSEhQqCuQcnP0CXZFcLGLonBBRri4uhHSg88kkBGuQe7EasLlCteQKBKtR3VFLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col0 = _t, Col1 = _t, Col2 = _t, Col3 = _t, Col4 = _t, Col5 = _t, Col6 = _t, Col7 = _t, Col8 = _t]),
    #"Transposed Table" = Table.Transpose(Source),
    #"Replaced Value" = Table.ReplaceValue(#"Transposed Table","",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Level"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Level", "Article"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Level", type text}, {"Article", type text}, {"Attribute", type text}, {"Value", type logical}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Attribute", Order.Ascending}, {"Level", Order.Ascending}, {"Article", Order.Ascending}})
in
    #"Sorted Rows"

[rohit_singh · Accepted solution]

Hello @joshua1990 ,

Please try the following M-code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwqyUzOSVXSUXIsKSnSUzCEs4zgLGM4y4RodbE6QLOdnIEiIUGhrkDKzdEn2BXBxS6KwgUZ4eLqRkgPPpNARrgGuROrCZcrXEOiSLQe1RWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"Level 1" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"Level 2" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"Level 1", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"Level 2", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Transposed Table", {{"Column1", each Text.BeforeDelimiter(_, "("), type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Extracted Text Before Delimiter","",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Down",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"-Article", type text}, {"Level 1-Attr. 1", type logical}, {"Level 1-Attr. 2", type logical}, {"Level 1-Attr. 3", type logical}, {"Level 1-Attr. 4", type logical}, {"Level 2-Attr. 1", type logical}, {"Level 2-Attr. 2", type logical}, {"Level 2-Attr. 3", type logical}, {"Level 2-Attr. 4", type logical}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"-Article"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Attribute.1", "Level"}, {"Attribute.2", "Attribute"}, {"-Article", "Article"}}),
    #"Removed Duplicates" = Table.Distinct(#"Renamed Columns")
in
    #"Removed Duplicates"

This is the final output:

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[AlexisOlson]

Looks like you beat me to it. What I came up with is pretty similar except without merging and later splitting two columns.