cancel
Showing results for 
Search instead for 
Did you mean: 
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
mcybulski
Solution Specialist
Solution Specialist

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

Jimmy801
Community Champion
Community Champion

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

7 REPLIES 7
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.

Jimmy801
Community Champion
Community Champion

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

mcybulski
Solution Specialist
Solution Specialist

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

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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors