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
mafioso
Helper II
Helper II

Delete every 2nd row in Power BI

Is it possible to delete every second row of my dataset? Every second row is a blank one so i want to delete it. 

1 ACCEPTED SOLUTION

@mafioso no problem, removing the empty rows is the same thing filtering them out based on the condition they empty, so as per my first response on the top of the column in query editor, the drop down arrow on the column select  remove empty, that will remove all empty rows from the dataset, where that column is empty, for anything other values you can also use the filter which is located in the same drop down as remove empty, and then also filter out any other values you dont want in the filter section?  make sense?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

9 REPLIES 9
ImkeF
Super User
Super User

You can use the function Table.AlternateRows for it like so:

 

Table.AlternateRows(Source,1,1,1)

This would keep the first row of table "Source" and delete the second, take the 3rd and continue to delete all even rows.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

vanessafvg
Super User
Super User

@mafioso  just say remove empty from the drop down arrow on the column

 

Capture.PNG





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Ok thank you! Moreover, is it possible to remove the rows with a certain value in a column? 

I want to remove every row which contains "null" or "0" in the hours column.

Thanks for your help!

@mafioso  you can easily filter out columns with those values just use the drop down arrow again on the column and there will be a filter option, you can untick all the values you not intersted in

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Yes I know that I can filter them but I don't need them anymore and that's why I think deleting them it is more efficient.

@mafioso so you want to delete the value and keep the row or filter out the row based on the value?

 

you can't delete it physically from your source file, you can only apply steps that will remove it from the dataset you end up with, filtering out the values you dont want is removing it from the dataset.

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@vanessafvg I want to do it the way I used to remove the empty rows. If a column contains a certain value I want to remove the row from the dataset. Or doesn't it make any difference if I use the filter? I am new in Power BI and that's why I'm not definitely sure. 

@mafioso no problem, removing the empty rows is the same thing filtering them out based on the condition they empty, so as per my first response on the top of the column in query editor, the drop down arrow on the column select  remove empty, that will remove all empty rows from the dataset, where that column is empty, for anything other values you can also use the filter which is located in the same drop down as remove empty, and then also filter out any other values you dont want in the filter section?  make sense?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@vanessafvg yes, thank you very much for your help!

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.