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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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