Forum Discussion

TaylorLile's avatar
TaylorLile
Frequent Visitor
6 years ago
Solved

Unpivot Two Groupings of Columns to Rows

Hello,   I am struggling with taking two groups of columns and converting them into only two columns. I have a decent understanding of unpivoting columns, but I'm not sure how to handle this when I...
Need Help
  • FrankAT's avatar
    FrankAT
    6 years ago

    Hi,

    here is the used M-Code:

    // Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCgkKdVXSgVFujj7BINoEiE2B2ACIDZVidaLRFcBoU7ACiAYjsEKYDJoGkEJjqGJjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Diabetes = _t, Hypertension = _t, Depression = _t, #"Diabetes Lab" = _t, #"Hypertension Lab" = _t, #"Depression Lab" = _t, ID = _t]),
    #"Reordered Columns" = Table.ReorderColumns(Source,{"ID", "Diabetes", "Hypertension", "Depression", "Diabetes Lab", "Hypertension Lab", "Depression Lab"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Diabetes", type logical}, {"Hypertension", type logical}, {"Depression", type logical}, {"Diabetes Lab", Int64.Type}, {"Hypertension Lab", Int64.Type}, {"Depression Lab", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"})
in
    #"Split Column by Delimiter"

// Lab Value (Reference of Table)
let
    Source = Table,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Attribute.2] = "Lab")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.2"})
in
    #"Removed Columns"

// Diagnosis (Reference of Table)
let
    Source = Table,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Attribute.2] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.2"})
in
    #"Removed Columns"

// Merged Lab Value & Diagnosis
let
    Source = Table.NestedJoin(#"Lab Value (Reference of Table)", {"ID", "Attribute.1"}, #"Diagnosis (Reference of Table)", {"ID", "Attribute.1"}, "Diagnosis", JoinKind.LeftOuter),
    #"Expanded Diagnosis" = Table.ExpandTableColumn(Source, "Diagnosis", {"Value"}, {"Value.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Diagnosis",{{"Attribute.1", "Condition"}, {"Value.1", "Diagnosis"}, {"Value", "Lab Value"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"ID", "Condition", "Diagnosis", "Lab Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"ID", Int64.Type}, {"Condition", type text}, {"Diagnosis", type logical}, {"Lab Value", Int64.Type}})
in
    #"Changed Type"

     

    Figures:

     

     

    Regards FrankAT

FrankAT's avatar
FrankAT
Icon for Community Champion rankCommunity Champion
6 years ago

Hi,

here is the used M-Code:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCgkKdVXSgVFujj7BINoEiE2B2ACIDZVidaLRFcBoU7ACiAYjsEKYDJoGkEJjqGJjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Diabetes = _t, Hypertension = _t, Depression = _t, #"Diabetes Lab" = _t, #"Hypertension Lab" = _t, #"Depression Lab" = _t, ID = _t]),
    #"Reordered Columns" = Table.ReorderColumns(Source,{"ID", "Diabetes", "Hypertension", "Depression", "Diabetes Lab", "Hypertension Lab", "Depression Lab"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Diabetes", type logical}, {"Hypertension", type logical}, {"Depression", type logical}, {"Diabetes Lab", Int64.Type}, {"Hypertension Lab", Int64.Type}, {"Depression Lab", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"})
in
    #"Split Column by Delimiter"

// Lab Value (Reference of Table)
let
    Source = Table,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Attribute.2] = "Lab")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.2"})
in
    #"Removed Columns"

// Diagnosis (Reference of Table)
let
    Source = Table,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Attribute.2] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.2"})
in
    #"Removed Columns"

// Merged Lab Value & Diagnosis
let
    Source = Table.NestedJoin(#"Lab Value (Reference of Table)", {"ID", "Attribute.1"}, #"Diagnosis (Reference of Table)", {"ID", "Attribute.1"}, "Diagnosis", JoinKind.LeftOuter),
    #"Expanded Diagnosis" = Table.ExpandTableColumn(Source, "Diagnosis", {"Value"}, {"Value.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Diagnosis",{{"Attribute.1", "Condition"}, {"Value.1", "Diagnosis"}, {"Value", "Lab Value"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"ID", "Condition", "Diagnosis", "Lab Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"ID", Int64.Type}, {"Condition", type text}, {"Diagnosis", type logical}, {"Lab Value", Int64.Type}})
in
    #"Changed Type"

 

Figures:

 

 

Regards FrankAT

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
6 years ago

Hi

posting my solution here, as amitchandak  called me in and I forgot to post my solution earlier :

 

let
    Source = Web.BrowserContents("https://community.powerbi.com/t5/Desktop/Unpivot-Two-Groupings-of-Columns-to-Rows/m-p/953043#M456617"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(9) > * > TR > :nth-child(1)"}, {"Column2", "TABLE:nth-child(9) > * > TR > :nth-child(2)"}, {"Column3", "TABLE:nth-child(9) > * > TR > :nth-child(3)"}, {"Column4", "TABLE:nth-child(9) > * > TR > :nth-child(4)"}, {"Column5", "TABLE:nth-child(9) > * > TR > :nth-child(5)"}, {"Column6", "TABLE:nth-child(9) > * > TR > :nth-child(6)"}, {"Column7", "TABLE:nth-child(9) > * > TR > :nth-child(7)"}}, [RowSelector="TABLE:nth-child(9) > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Diabetes", type logical}, {"Hypertension", type logical}, {"Depression", type logical}, {"Diabetes Lab", Int64.Type}, {"Hypertension Lab", Int64.Type}, {"Depression Lab", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,"Diagnosis",Replacer.ReplaceValue,{"Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute.2]), "Attribute.2", "Value")
in
    #"Pivoted Column"