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
Anonymous
Not applicable

How to dynamically delete Top Rows *Before a specific value in a Column1 in the below example.

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 NumberAmount
123
234
125
236
127
238
129
2310
1211
2312
2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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.

 

View solution in original post

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

View solution in original post

8 REPLIES 8
Mariusz
Community Champion
Community Champion

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"

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

Anonymous
Not applicable

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

Anonymous
Not applicable

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.

Anonymous
Not applicable

Thank you, simple and straight forword solution. 

watkinnc
Super User
Super User

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

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Jimmy801
Community Champion
Community Champion

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

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.

Top Solution Authors
Top Kudoed Authors