Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
How to dynamically delete Top Rows before a specific value(ex: ID Number) in Column1 in the below example :
I have data set like below. Sometimes the Rownumber of "ID Number" changes. Havning difficulty to delete TopRows using GUI function in Power Query. Can someone help how to find the "ID Number" column using Index or find function.
Below is sample data. Appreciate any help on this!
unwanted | |
more | |
unwanted | |
ID Number | Amount |
12 | 3 |
23 | 4 |
12 | 5 |
23 | 6 |
12 | 7 |
23 | 8 |
12 | 9 |
23 | 10 |
12 | 11 |
23 | 12 |
Solved! Go to Solution.
Start by removing rows from the gui by putting in any number. Power query will write a line like below.
#"Removed Top Rows" = Table.Skip(Source,2)
Modify it to be the function each [Column1] <> "ID Number"
#"Removed Top Rows" = Table.Skip(Source,each [Column1] <> "ID Number")
Note that this code will work as long as all of the data in the column is text.
Hello @Anonymous
this code is created automatically by Power BI when you manully input data. I prefer to do this way to give everything the possibility to understand the solution. In future I will use the #table function to make it more clearly.
If my post works as well, I would appreciate it you marking it as solution as well or to give a like.
All the best
Jimmy
Hi @Anonymous
You can try the below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKs0rT8wrSU1R0lFSitWJVsrNL0qFsdHlPF0U/Epzk1KLgAKOufmleSVgYUMjIN8YzDQyBjJNEKKmCFEzhKg5QtQCIWqJEDU0QAgbGiKJGynFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Column2] <> "")),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID Number", Int64.Type}, {"Amount", Int64.Type}})
in
#"Changed Type"
I was still trying to understand how was the source getting feed. How was json is converting to table.
But it worked as well.
Hello @Anonymous
this code is created automatically by Power BI when you manully input data. I prefer to do this way to give everything the possibility to understand the solution. In future I will use the #table function to make it more clearly.
If my post works as well, I would appreciate it you marking it as solution as well or to give a like.
All the best
Jimmy
Start by removing rows from the gui by putting in any number. Power query will write a line like below.
#"Removed Top Rows" = Table.Skip(Source,2)
Modify it to be the function each [Column1] <> "ID Number"
#"Removed Top Rows" = Table.Skip(Source,each [Column1] <> "ID Number")
Note that this code will work as long as all of the data in the column is text.
Thanks for this. What a great Resource. I am struggling to filter some data that comes with two clients.
Thank you, simple and straight forword solution.
After initially loading the table, you could select the "ID Number"column, and right click and "Add as New Query". Rename it named SkipRows, or something, then:
//Add these steps to the new single column (you might have to make it a table first, I'm not at my PC!
AddIndex=Table.AddIndexColumn(Source, "Rows", 0,1),
FindRow=Table.SelectRows(AddIndex, each [NameOfIDColumn] = "ID Number")
Then, click right click the number in the "Rows" column and select "Drill Down", which will give you a number value.
Now, go back to your main table, and after your source step, add:
Skipped = Table.SkipFirstN(Source, RowsToSkip),
The RowsToSkip value should now be the zero based index number of the row with "ID Number", and since it starts at zero, the number of rows to skip will match up with the "Rows" number.
I think Ken Puls might have written something like this at some point.
-Nate
Hello @Anonymous
use Table.RemoveFirstN and List.PositionOf to solve this issue. Here an example what I mean, supposing your column name is "Column1". By the way it would be also possible to solve it when you even don't know the column name.
Here an example
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKs0rT8wrSU1R0lFSUIrViVbKzS9KhXMwZD1dFPxKc5NSi4Aijrn5pXklYGFDIyDfGMw0MgYyTRCipghRM4SoOULUAiFqiRA1NEAIGxoiiRspxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
DeleteUntilIDNumber = Table.RemoveFirstN(Source,List.PositionOf(Source[Column1],"ID Number")),
PromoteHeader = Table.PromoteHeaders(DeleteUntilIDNumber, [PromoteAllScalars=true])
in
PromoteHeader
Copy paste this code to the advanced editor to see how the solution works
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy