Hi all!I would like to transform a specific cross table into a list.This is the structure of the pivot table: Level 1Level 2ArticleAttr. 1Attr. 2Attr. 3Attr. 4Attr. 1Attr. 2Attr. 3Attr. 4ABCTRUEFALSETRUETRUEFALSETRUEFALSETRUEDEFTRUETRUEFALSETRUETRUEFALSETRUEFALSEERGFALSETRUETRUEFALSETRUETRUEFALSETRUEETZTRUEFALSETRUETRUEFALSETRUETRUEFALSE And I would like to get this structure in the end:ArticleLevelAttributeValueABCLevel 1Attr. 1TRUEABCLevel 1Attr. 2FALSEABCLevel 1Attr. 3TRUEABCLevel 1Attr. 4TRUEABCLevel 2Attr. 1FALSEABCLevel 2Attr. 2TRUEABCLevel 2Attr. 3FALSEABCLevel 2Attr. 4TRUE How is this possible in Power Query?

joshua1990

Post Prodigy

4 years ago

Solved

Unpivot 2 level Cross Table

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?

Need Help

rohit_singh

4 years ago

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! 😊

3 Replies

rohit_singh

Solution Sage

4 years ago

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
Super User
4 years ago
Looks like you beat me to it. What I came up with is pretty similar except without merging and later splitting two columns.

AlexisOlson

Super User

4 years ago

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"

Forum Discussion

Unpivot 2 level Cross Table

3 Replies

Related content

Pivot / Unpivot?

Unpivot Column

Unpivot Question

Unpivot

Cross datawarehouse queries