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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
nawaz_atea
Frequent Visitor

How to shift cells up or down in Power BI?

Hi,

 

I have a table as below and I just wanted to shift cells to the up rows. I tried to remove empty cells but it's effecting other rows and column as well. Is there any easy option to shift cells value just to one or two rows up as I have indicated below.

 

I think there must be the easiest way to do so.

 

I hope it's easy to understand 🙂

 

Thanks in advance!!  

 

 

InkedCapture_LI.jpg

 

 

3 ACCEPTED SOLUTIONS
BeemsC
Resolver III
Resolver III

If it's like above, you should be able to do it by removing blank rows in query editor

View solution in original post

Hi,

 

Thank you for your reply!

 

I tried at doing that but it has a impact on all rows and it's look like below.

 

Capture2.PNG

View solution in original post

3 Options:

 

1. Use fill Up:

 

let
    Source = Table1,
    #"Filled Up" = Table.FillUp(Source,{"Tx Current value", "Rx Current value"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([ipAddress] <> null))
in
    #"Filtered Rows"

 

2. Add Index columns and join the table with itself:

 

let
    Source = Table1,
    #"Added Index" = Table.AddIndexColumn(Source, "Index0", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index1", 1, 1),
    #"Added Index2" = Table.AddIndexColumn(#"Added Index1", "Index2", 2, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index2",{"Index2"},#"Added Index2",{"Index0"},"SecondNextRow",JoinKind.LeftOuter),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries",{"Index1"},#"Merged Queries",{"Index0"},"NextRow",JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries1",{"Tx Current value", "Rx Current value", "Index0", "Index1", "Index2"}),
    #"Expanded SecondNextRow" = Table.ExpandTableColumn(#"Removed Columns", "SecondNextRow", {"Tx Current value"}, {"Tx Current value"}),
    #"Expanded NextRow" = Table.ExpandTableColumn(#"Expanded SecondNextRow", "NextRow", {"Rx Current value"}, {"Rx Current value"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded NextRow", each ([ipAddress] <> null))
in
    #"Filtered Rows"

 

3. Use Table.Partition and merge the partitions on ipAddres and itName:

 

let
    Source = Table1,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Filled Down" = Table.FillDown(#"Added Index",{"ipAddress", "itName"}),
    Partitions = Table.Partition(#"Filled Down","Index",3, each _),
    Partitions1 = Partitions{0},
    #"Removed Columns" = Table.RemoveColumns(Partitions1,{"Tx Current value", "Rx Current value", "Index"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"ipAddress", "itName"},Partitions{2},{"ipAddress", "itName"},"Partition2",JoinKind.LeftOuter),
    #"Expanded Partition2" = Table.ExpandTableColumn(#"Merged Queries", "Partition2", {"Tx Current value"}, {"Tx Current value"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Partition2",{"ipAddress", "itName"},Partitions{1},{"ipAddress", "itName"},"Expanded Partition2",JoinKind.LeftOuter),
    #"Expanded Expanded Partition2" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Partition2", {"Rx Current value"}, {"Rx Current value"})
in
    #"Expanded Expanded Partition2"
Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
BeemsC
Resolver III
Resolver III

If it's like above, you should be able to do it by removing blank rows in query editor

Hi,

 

Thank you for your reply!

 

I tried at doing that but it has a impact on all rows and it's look like below.

 

Capture2.PNG

3 Options:

 

1. Use fill Up:

 

let
    Source = Table1,
    #"Filled Up" = Table.FillUp(Source,{"Tx Current value", "Rx Current value"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([ipAddress] <> null))
in
    #"Filtered Rows"

 

2. Add Index columns and join the table with itself:

 

let
    Source = Table1,
    #"Added Index" = Table.AddIndexColumn(Source, "Index0", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index1", 1, 1),
    #"Added Index2" = Table.AddIndexColumn(#"Added Index1", "Index2", 2, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index2",{"Index2"},#"Added Index2",{"Index0"},"SecondNextRow",JoinKind.LeftOuter),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries",{"Index1"},#"Merged Queries",{"Index0"},"NextRow",JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries1",{"Tx Current value", "Rx Current value", "Index0", "Index1", "Index2"}),
    #"Expanded SecondNextRow" = Table.ExpandTableColumn(#"Removed Columns", "SecondNextRow", {"Tx Current value"}, {"Tx Current value"}),
    #"Expanded NextRow" = Table.ExpandTableColumn(#"Expanded SecondNextRow", "NextRow", {"Rx Current value"}, {"Rx Current value"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded NextRow", each ([ipAddress] <> null))
in
    #"Filtered Rows"

 

3. Use Table.Partition and merge the partitions on ipAddres and itName:

 

let
    Source = Table1,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Filled Down" = Table.FillDown(#"Added Index",{"ipAddress", "itName"}),
    Partitions = Table.Partition(#"Filled Down","Index",3, each _),
    Partitions1 = Partitions{0},
    #"Removed Columns" = Table.RemoveColumns(Partitions1,{"Tx Current value", "Rx Current value", "Index"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"ipAddress", "itName"},Partitions{2},{"ipAddress", "itName"},"Partition2",JoinKind.LeftOuter),
    #"Expanded Partition2" = Table.ExpandTableColumn(#"Merged Queries", "Partition2", {"Tx Current value"}, {"Tx Current value"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Partition2",{"ipAddress", "itName"},Partitions{1},{"ipAddress", "itName"},"Expanded Partition2",JoinKind.LeftOuter),
    #"Expanded Expanded Partition2" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Partition2", {"Rx Current value"}, {"Rx Current value"})
in
    #"Expanded Expanded Partition2"
Specializing in Power Query Formula Language (M)

Hi!

 

Thanks a lot for your answer and script! 

 

I tried with edit query to fill up and removed null rows and that worked exactly what I wanted. which is like your option 1 (Use fill up)

 

 

//Nawaz

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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