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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.