Unpivot Two Groupings of Columns to Rows

[TaylorLile]

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 have to do it another time because then I end up with an obserd number of rows. 

Here is what I'm trying to do in simple terms:

I have the following:

ID	Diabetes	Hypertension	Depression	Diabetes Lab	Hypertension Lab	Depression Lab
1	TRUE	TRUE	FALSE	4	5	0
2	TRUE	FALSE	FALSE	5	1	4
3	FALSE	TRUE	FALSE	5	3	1

I'm trying to convert this to look like below:

ID	Condition	Diagnosis	Lab Value
1	Diabetes	TRUE	4
1	Hypertension	TRUE	5
1	Depression	FALSE	0
2	Diabetes	TRUE	5
2	Hypertension	FALSE	1
2	Depression	FALSE	4
3	Diabetes	FALSE	5
3	Hypertension	TRUE	3
3	Depression	FALSE	1

First, I unpivoted the Diabetes, Hypertension and Depression columns to create those Condition and Diagnosis columns (Which is exactly what I need). However, I'm still left with the lab values and am unsure on how to unpivot. If I unpivot just those three lab columns I end up with the wrong results. 

Let me know if you have any advice!! Thanks!

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"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.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter",null,"Diagnosis",Replacer.ReplaceValue,{"Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute.2]), "Attribute.2", "Value")
in
    #"Pivoted Column"

Hope this helps.

[FrankAT]

Hi Ashish_Mathur,

thx, I learned something again about pivoting data. I think your solution is the most efficient.

Regards Frank AT

[Ashish_Mathur]

Thank you.  If my reply helped, please mark it as Answer.

[smpa01]

@TaylorLile  please try this

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}}),
    Custom1 = Table.DemoteHeaders(#"Changed Type"),
    #"Transposed Table" = Table.Transpose(Custom1),
    #"Filtered Rows" = Table.SelectRows(#"Transposed Table", each ([Column1] <> "Depression Lab" and [Column1] <> "Diabetes Lab" and [Column1] <> "Hypertension Lab")),
    #"Transposed Table1" = Table.Transpose(#"Filtered Rows"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers1", {"ID"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"ID", Int64.Type}}),
    #"Trimmed Text1" = Table.TransformColumns(#"Changed Type1",{{"Attribute", Text.Trim, type text}}),
    Custom2 = Table.SelectRows(#"Transposed Table", each [Column1] = "Depression Lab" or [Column1] = "Diabetes Lab" or [Column1] = "Hypertension Lab" or [Column1] = "ID"),
    #"Transposed Table2" = Table.Transpose(Custom2),
    #"Promoted Headers2" = Table.PromoteHeaders(#"Transposed Table2", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(#"Promoted Headers2", {"ID"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns1","Lab","",Replacer.ReplaceText,{"Attribute"}),
    #"Trimmed Text" = Table.TransformColumns(#"Replaced Value",{{"Attribute", Text.Trim, type text}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Trimmed Text",{{"ID", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Trimmed Text1", {"ID", "Attribute"}, #"Trimmed Text", {"ID", "Attribute"}, "Replaced Value", JoinKind.LeftOuter),
    #"Expanded Replaced Value" = Table.ExpandTableColumn(#"Merged Queries", "Replaced Value", {"Value"}, {"Lab Value"})
in
    #"Expanded Replaced Value"

[amitchandak]

Refer this : https://radacad.com/pivot-and-unpivot-with-power-bi

[TaylorLile]

Thanks @amitchandak , but I'm still not sure this solves my issue. If I follow the instruction in the article, don't I run into the same issue I talked about earlier where I have duplicated rows that don't necessarily match each other like below?

IDConditionDiagnosisCondition LabLab Value

1	Hypertension	TRUE	Diabetes Lab	4
1	Hypertension	TRUE	Hypertension Lab	5
1	Hypertension	TRUE	Depression Lab	0
1	Diabetes	TRUE	Diabetes Lab	4
1	Diabetes	TRUE	Hypertension Lab	5
1	Diabetes	TRUE	Depression Lab	0
1	Depression	FALSE	Diabetes Lab	4
1	Depression	FALSE	Hypertension Lab	5
1	Depression	FALSE	Depression Lab	0
2	Hypertension	FALSE	Diabetes Lab	5
2	Hypertension	FALSE	Hypertension Lab	1
2	Hypertension	FALSE	Depression Lab	4
2	Diabetes	TRUE	Diabetes Lab	5
2	Diabetes	TRUE	Hypertension Lab	1
2	Diabetes	TRUE	Depression Lab	4
2	Depression	FALSE	Diabetes Lab	5
2	Depression	FALSE	Hypertension Lab	1
2	Depression	FALSE	Depression Lab	4
3	Hypertension	TRUE	Diabetes Lab	5
3	Hypertension	TRUE	Hypertension Lab	3
3	Hypertension	TRUE	Depression Lab	1
3	Diabetes	FALSE	Diabetes Lab	5
3	Diabetes	FALSE	Hypertension Lab	3
3	Diabetes	FALSE	Depression Lab	1
3	Depression	FALSE	Diabetes Lab	5
3	Depression	FALSE	Hypertension Lab	3
3	Depression	FALSE	Depression Lab	1

[FrankAT · Accepted solution]

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]

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"

[amitchandak]

You can use something like this in DAX. Instead of tablename[columnname], I just used [columnname]

union(
 selectcolumn(table,"ID",[ID],"Condition","Diabetes","Diagnosis",[Diabetes],"Lab Value",[Diabetes Lab])
 ,selectcolumn(table,"ID",[ID],"Condition","Hypertension","Diagnosis",[Hypertension],"Lab Value",[Hypertension Lab])
 ,selectcolumn(table,"ID",[ID],"Condition","Depression ","Diagnosis",[Depression ],"Lab Value",[Depression  Lab])
 )

But for M code

@ImkeF , can you help?