Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Scotsman
New Member

Power Query - How to move data to new row and new column?

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

 

Scotsman_0-1638806543504.png

 

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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:

Eyelyn9_0-1639032658239.png

 


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.

mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI 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.

Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

Check out the May 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.