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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.