Showing results for 
Search instead for 
Did you mean: 
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

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

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.



    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"})
    #"Replaced Value"






Microsoft Employee
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.



Microsoft Employee

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors