How to shift cells up or down in Power BI?

[nawaz_atea]

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!!  

[BeemsC · Accepted solution]

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

[nawaz_atea · Accepted solution]

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.

[BeemsC · Accepted solution]

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

[nawaz_atea · Accepted solution]

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.

[MarcelBeug · Accepted solution]

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"

[nawaz_atea]

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