Learn from the best! Meet the four finalists headed to the FINALS of the Power BI Dataviz World Championships! 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])
A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.
Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.
| User | Count |
|---|---|
| 5 | |
| 4 | |
| 4 | |
| 3 | |
| 2 |
| User | Count |
|---|---|
| 11 | |
| 10 | |
| 7 | |
| 7 | |
| 6 |