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
I want to recode text variables into columns.
The last image is my expectation "graphically" before I can unpivot.
It the expectation possible
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
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
63 | |
54 | |
27 | |
17 | |
13 |