Unpivoting numeric and text columns

[bkgwadi]

I want to recode text variables into columns.

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

It the expectation possible

[bkgwadi]

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]

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"

Pat

[bkgwadi]

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.

[ppm1]

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