Filter Out a Column Value Identical in Name with Another Value in the Same Column

[Anonymous]

Hi and appreciate some Power Q help. I want to filter out the bottom three rows to just show the top three rows re Indigenous status, including the ‘Not Stated’ row on the third row. However I’m not sure how to filter out the ‘Not Stated’ on the final row (which relates to the Need for Assistance rows) without also filtering out the ‘Not Stated’ for the Indigenous rows. Thanks in advance for the response.

[wdx223_Daniel · Accepted solution]

NewStep=Table.FromRecords(List.Accumulate(Table.ToRecords(PreviousStepName),{{},[]},(x,y)=>{if Record.HasField(x{1},y[Category]) then x{0} else x{0}&{y},if y[Indigenous Status]<>"Not Stated" or Record.HasField(x{1},y[Category]) then x{1} else x{1}&Record.AddField([],y[Category],"")}){0})

[Anonymous]

Hi again Daniel and how do i amend for doing the same thing with the Assistance values? That is, I changed the column [Indigenous Status] in your formula to a new column [Assistance Status] i created. I thought that would have filtered out the Indigenous related values, including the Not Stated data, related to those. And kept in all the Assistance Value data. But it just returned the same Indigenous values and filtered out the Assistance values. Appreciate your thoughts? Thanks again.

[Anonymous]

Hi bud and nice work, that worked fine! Appreciate the quick response.

[jennratten]

Are the values in the Category column all the same?  Are the values of the Indigeous States really grouped values (rows 1-3 go together and 4-6 go together)?  How does 'Not Stated' relate to Non-Indigeous vs. 'Does Not Have Need for Assistance...'?

[Anonymous]

Hi Jen and thanks a lot for taking a look. The Category values are different.  Also, the data for 'Not Stated' under the Indigenous and Does Not Have Need is different. That seems the tricky part because if you total the 'Not Stated' value, it would combine the Indigenous and Does Not Have Need together, which wouldn't be accurate. What i'm aiming to do is to filter out the Does Not Have Need values, including the 'Not Expected' data related to that, so i can then create a new query that only includes the Indigenous data. Then i'll do the same thing with the Does Not Have Need data (i.e. filter out the Indigenous values and create a query with only the Does Not Have Need values). Thanks again.

A larger snapshot of the data is below.