Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |