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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
v-eqin-msft
Community Support
Community Support

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors