cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ovetteabejuela Established Member
Established Member

Power Query - Delete After this Row

(PBIX File) Remove After This Row

 

How do you delete the remaining rows after an instance of an entry. For example if I found the entry "DeleteStartingThisRow" PowerQuery will grab the row number and delete all the entries starting from that row until the last entry.

3 ACCEPTED SOLUTIONS

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: Power Query - Delete After this Row

No need for Power Query to grab the row number.

You can add a step by choosing Keep Rows - Keep Top Rows on the Home tab, just enter a dummy number and then adjust the code to:

 

= Table.FirstN(#"Changed Type",each [Header] <> "DeleteStartingThisRow")
Specializing in Power Query Formula Language (M)

View solution in original post

MarcelBeug Super Contributor
Super Contributor

Re: Power Query - Delete After this Row

You need a function in which the required transformations are done.

Next you can use this function for all (selected) files in the folder.

 

This will be done for you if you expand the binaries column from the navigation table with the files in your folder.

In this topic I explained this functionality, that was introduced with the November 2016 Update.

This will create some objects, including a "Transform Sample Binary" Query in which you shoud apply your transformations.

These transformations will be automatically propagated to the function that is used to expand all binaries.

 

However this will only work if you expand 1 type of binary (only csv or only xlsx or only ....).

If you have mixed extensions, then you need to create similar functionality yourself.

Let me know if this is the case and if you need more information on how to do that,

I may not be able to respond within a few hours though.

 

Specializing in Power Query Formula Language (M)

View solution in original post

MarcelBeug Super Contributor
Super Contributor

Re: Power Query - Delete After this Row

It looks like you are not using the Combine Binaries functionality how it's supposed to be used.

 

Just take a look at this video.

Specializing in Power Query Formula Language (M)

View solution in original post

9 REPLIES 9
MarcelBeug Super Contributor
Super Contributor

Re: Power Query - Delete After this Row

No need for Power Query to grab the row number.

You can add a step by choosing Keep Rows - Keep Top Rows on the Home tab, just enter a dummy number and then adjust the code to:

 

= Table.FirstN(#"Changed Type",each [Header] <> "DeleteStartingThisRow")
Specializing in Power Query Formula Language (M)

View solution in original post

ovetteabejuela Established Member
Established Member

Re: Power Query - Delete After this Row

Awesome! Absolutely Awesome! Thanks @MarcelBeug

ovetteabejuela Established Member
Established Member

Re: Power Query - Delete After this Row

@MarcelBeug, I have to get back to you on this... I initially thought this is working for me, but later did I found out that it isn't.

 

Well if it's only for just one file it would work but loading more file(CSV,XLSX) into the source folder everything(every row) below that "string" will be discarded, so no matter how many files is accumulated in that folder, my number of records is only that TopN rows constantly.

 

What do you think is the workaround for this?

MarcelBeug Super Contributor
Super Contributor

Re: Power Query - Delete After this Row

You need a function in which the required transformations are done.

Next you can use this function for all (selected) files in the folder.

 

This will be done for you if you expand the binaries column from the navigation table with the files in your folder.

In this topic I explained this functionality, that was introduced with the November 2016 Update.

This will create some objects, including a "Transform Sample Binary" Query in which you shoud apply your transformations.

These transformations will be automatically propagated to the function that is used to expand all binaries.

 

However this will only work if you expand 1 type of binary (only csv or only xlsx or only ....).

If you have mixed extensions, then you need to create similar functionality yourself.

Let me know if this is the case and if you need more information on how to do that,

I may not be able to respond within a few hours though.

 

Specializing in Power Query Formula Language (M)

View solution in original post

ovetteabejuela Established Member
Established Member

Re: Power Query - Delete After this Row

Hi @MarcelBeug

 

I'm about to show this, but you've got a response already. But this is what I did, just maybe a reference to everyone who read this topic.

 

Version 2, Applied Marcel's Solution

ovetteabejuela Established Member
Established Member

Re: Power Query - Delete After this Row

I think I know what you're talking about, let me try though...

ovetteabejuela Established Member
Established Member

Re: Power Query - Delete After this Row

I think I have a question already, in my example(attached) I expanded the Table and not the Binary what is the difference? I'm researching after this though...

 

Expand Binary or Table.PNG

MarcelBeug Super Contributor
Super Contributor

Re: Power Query - Delete After this Row

It looks like you are not using the Combine Binaries functionality how it's supposed to be used.

 

Just take a look at this video.

Specializing in Power Query Formula Language (M)

View solution in original post

ovetteabejuela Established Member
Established Member

Re: Power Query - Delete After this Row

Hi Marcel,

 

I just did and yes I did learn from it.

 

I already applied the solution at the sample file so your proposed solution will be applied at the each single file and it went well.

 

Thank you very much for sharing.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)