Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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!!
Solved! Go to Solution.
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.
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"
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.
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"
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
120 | |
112 | |
60 | |
58 |