This time we’re going bigger than ever. Fabric, Power BI, SQL, AI and more. We're covering it all. You won't want to miss it.
Learn moreJoin the FabCon + SQLCon recap series. Up next: Power BI, Real-Time Intelligence, IQ and AI, and Data Factory take center stage. All sessions are available on-demand after the live show. Register now
I am trying to find an efficient way to filter a data set using powerquery. I need to filter out all rows representing a company only when there is no data in another column. But I need to retain all the rows of a company when there is at least one row of data in that column. So for the below table, I need to filter out all rows of Company B as it does not have any data in the "Descr" column. But I need to retain all rows of Company A (including those where the Descr column is empty) and Company C.
I am currently using a two-step process. The first step involves finding companies like A and C by filtering the Descr column for non-null values and then creating a company list (after removing the duplicates). Then I apply inner join as seen below to filter out Company B.
Is there a straightforward way to perform this filter?
Thank you.
Solved! Go to Solution.
NewStep=Table.Combine(Table.Group(YourTable,"Company Name",{"n",each if List.NonNullCount([Descr])=0 the #table(0,{}) else _)})[n])
Thank you. It did work. But it does take time for a large data set over 100K rows.
NewStep=Table.Combine(Table.Group(YourTable,"Company Name",{"n",each if List.NonNullCount([Descr])=0 the #table(0,{}) else _)})[n])
Check out the April 2026 Power BI update to learn about new features.
If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.
A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.
| User | Count |
|---|---|
| 3 | |
| 3 | |
| 3 | |
| 2 | |
| 2 |