Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
bkgwadi
Frequent Visitor

Unpivoting numeric and text columns

I want to recode text variables into columns.Request for assistance_Pivoting.jpgRequest for assistance_Expectation.jpg

The last image is my expectation "graphically" before I can unpivot. 

 

It the expectation possible

4 REPLIES 4
bkgwadi
Frequent Visitor

Yes, I did use it. Indeed it was very complicated. Worse if you have to group data in rows and try to "columnise" the millions of options

ppm1
Solution Sage
Solution Sage

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcHBBanJmWmVhkqxOtFKRlBBAygGCxpj12GkFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Num = _t, Option1 = _t, Option2 = _t, OtherOption = _t, Specify = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OtherOption", Int64.Type}, {"Num", Int64.Type}, {"Option1", Int64.Type}, {"Option2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [OtherOption] = 0 then null else Record.FromList({[OtherOption]}, {[Specify]})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"OtherOption", "Specify"}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"Specify1", "Specify2"}, {"Specify1", "Specify2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Specify1", Int64.Type}, {"Specify2", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,0,Replacer.ReplaceValue,{"Specify1", "Specify2"})
in
    #"Replaced Value"

 

ppm1_0-1685016245938.png

 

Pat

 

Microsoft Employee
bkgwadi
Frequent Visitor

Thank you.

 

It is way too advanced for a beginner like me but it is one step for mankind for me. Let me learn a few steps in coding.

It is fairly advanced, but it's not a straight forward scenario. Were you able to apply it? The key step is the Added Custom step. From there you just expand to do regular power query stuff.

 

Pat

Microsoft Employee

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors