Don't miss your chance to take the Fabric Data Engineer (DP-700) exam on us!
Learn moreWe've captured the moments from FabCon & SQLCon that everyone is talking about, and we are bringing them to the community, live and on-demand. Starts on April 14th. 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])
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.
Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.
| User | Count |
|---|---|
| 5 | |
| 3 | |
| 3 | |
| 2 | |
| 2 |
| User | Count |
|---|---|
| 9 | |
| 8 | |
| 7 | |
| 7 | |
| 5 |