Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
I have data that I'd like to move but I can't figure out how. I can move to a new column no problem, but I can't get it to move to different rows as shown. Ideas appreciated.
thanks
Solved! Go to Solution.
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("i45WMlTSUQrJyMxLVwCxDA2AhIVSrE60khEuCWNMCUuwBMIokF5DQyxGgSWMsBgFljCGGBULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Part Number" = _t, Part = _t, Depth = _t, Height = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Part Number", Int64.Type}, {"Part", type text}, {"Depth", Int64.Type}, {"Height", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Part Number"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Attribute] = "Part" then [Value] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Value] <> "Thing 1" and [Value] <> "Thing 2")),
#"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Dimension", each if
[Custom] = "Thing 1" and [Attribute] = "Depth" then "Depth" else
if [Custom] = "Thing 1" and [Attribute] = "Height" then "Height" else
if [Custom] = "Thing 2" and [Attribute] = "Depth" then "Width" else
if [Custom] = "Thing 2" and [Attribute] = "Height" then "Remove" else null),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([Dimension] <> "Remove")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Part Number", "Dimension", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Dimension]), "Dimension", "Value")
in
#"Pivoted Column"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @Scotsman ,
You could also use DAX to add calculated columns instead:
Rank = CONVERT( RIGHT([Part],1),INTEGER)
Width = LOOKUPVALUE('Table'[Depth],[Part Number],[Part Number],[Rank],[Rank]+1, BLANK())
Height2 = IF([Rank]=1,[Height],BLANK())
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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("i45WMlTSUQrJyMxLVwCxDA2AhIVSrE60khEuCWNMCUuwBMIokF5DQyxGgSWMsBgFljCGGBULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Part Number" = _t, Part = _t, Depth = _t, Height = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Part Number", Int64.Type}, {"Part", type text}, {"Depth", Int64.Type}, {"Height", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Part Number"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Attribute] = "Part" then [Value] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Value] <> "Thing 1" and [Value] <> "Thing 2")),
#"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Dimension", each if
[Custom] = "Thing 1" and [Attribute] = "Depth" then "Depth" else
if [Custom] = "Thing 1" and [Attribute] = "Height" then "Height" else
if [Custom] = "Thing 2" and [Attribute] = "Depth" then "Width" else
if [Custom] = "Thing 2" and [Attribute] = "Height" then "Remove" else null),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([Dimension] <> "Remove")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Part Number", "Dimension", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Dimension]), "Dimension", "Value")
in
#"Pivoted Column"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks for the response - it will take me some time to apply this bc I am noob. I understand the filtering and the formula, but still trying to get my head around pivoting. I'll get it though - thanks so much.