Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello
I'd like to do the transformation shown below in Power BI, i.e. have a single row for each "Number" value but split the number and letter values in column B into two columns. Would greatly appreciate any help with this.
Many thanks
Tim
Solved! Go to Solution.
Hi @tgjones43,
In the Power query add the following column:
if (try Number.From([WFD]) otherwise 0) = 0 then "WFD Name" else "WFD Number"
Then pivot columns without aggregation.
See M code for full example:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYHQjLEcwyArKM4CwnMMsYyDKGs5yVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, WFD = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"WFD", type text}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each if (try Number.From([WFD]) otherwise 0) = 0 then "WFD Name" else "WFD Number"), #"Pivoted Column" = Table.Pivot(#"Added Custom1", List.Distinct(#"Added Custom1"[Custom]), "Custom", "WFD") in #"Pivoted Column"
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @tgjones43,
Using the same logic you first need to do the Unpivot of your WFD colums, so you will get 2 columns Value and Attribute then just use the same formula with some adjustment o have the WFD 1 or 2 in your names. Check the M code for the column and the full data treatement.
if (try Number.From([Value]) otherwise 0) = 0 then [Attribute]& " Name" else [Attribute] & " Number"
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMTpVgdCM8RiF3APCMgC4RN4TwnIHYF84yBLBA2g/OcgdhNKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, WFD = _t, WFD2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"WFD", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Number"}, "Attribute", "Value"), #"Added Custom1" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if (try Number.From([Value]) otherwise 0) = 0 then [Attribute]& " Name" else [Attribute] & " Number"), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value") in #"Pivoted Column"
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @tgjones43,
In the Power query add the following column:
if (try Number.From([WFD]) otherwise 0) = 0 then "WFD Name" else "WFD Number"
Then pivot columns without aggregation.
See M code for full example:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYHQjLEcwyArKM4CwnMMsYyDKGs5yVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, WFD = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"WFD", type text}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each if (try Number.From([WFD]) otherwise 0) = 0 then "WFD Name" else "WFD Number"), #"Pivoted Column" = Table.Pivot(#"Added Custom1", List.Distinct(#"Added Custom1"[Custom]), "Custom", "WFD") in #"Pivoted Column"
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you so much MFelix, that helps a lot. Actually though, my data is a little more complicated. The transformation I want to do is shown below - there is a second column (column C, "WFD2") that needs transforming in the same way that column B was - is this possible?
Tim
Hi @tgjones43,
Using the same logic you first need to do the Unpivot of your WFD colums, so you will get 2 columns Value and Attribute then just use the same formula with some adjustment o have the WFD 1 or 2 in your names. Check the M code for the column and the full data treatement.
if (try Number.From([Value]) otherwise 0) = 0 then [Attribute]& " Name" else [Attribute] & " Number"
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMTpVgdCM8RiF3APCMgC4RN4TwnIHYF84yBLBA2g/OcgdhNKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, WFD = _t, WFD2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"WFD", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Number"}, "Attribute", "Value"), #"Added Custom1" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if (try Number.From([Value]) otherwise 0) = 0 then [Attribute]& " Name" else [Attribute] & " Number"), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value") in #"Pivoted Column"
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
85 | |
76 | |
69 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |