Forum Discussion
Create a list from one column excluding values from another
Let me guess, you got your inspiration from Chris Webb's BI Blog: The List.* M Functions And The equationCriteria Argument Chris Webb's BI Blog (crossjoin.co.uk) ?
let Source = {"Apples", "Zebras", "Bananas", "Veggies"}, Select = List.Select(Source, each Table.RowCount(Table.SelectRows(QueryA, (k)=> Text.StartsWith( _ ,k[Column1])))=0) in Selectlet QueryB = {"Apples", "Zebras", "Bananas", "Veggies", "Pears", "Pea"}, Source = #table({"Text"}, {{"App"}, {"Pe"}, {"Ban"}, {"All Others"}}), #"Added Custom" = Table.AddColumn( Source, "Column list", (m) => if m[Text] = "All Others" then List.Select( QueryB, each Table.RowCount(Table.SelectRows(Source, (k) => Text.StartsWith(_, k[Text]))) = 0 ) else List.Select(QueryB, each Text.StartsWith(_, m[Text])) ) in #"Added Custom"
let
Source = {"Apples", "Zebras", "Bananas", "Veggies"},
sample list - can be replaced with your actual source
Select = List.Select(Source,
The task is to filter out the items that match the pattern. But you can also formulate the task differently - keep only the items that do NOT match the pattern. List.Select uses a True/False check to decide what to keep, so we create the formula accordingly
each Table.RowCount(...) = 0
Now for each item of the list in Source we are lookig up the values that may match in QueryA. Since we are already in a loop ("each") and we want to avoid confusion about filter contexts we now use a direct addressed filter context "(k)=>" instead of "each" for the inner loop. "k" is arbitrary, it can be anything, but it allows to clearly reference the context.
Table.SelectRows(QueryA, (k) => Text.StartsWith(_, k[Column1]))
From QueryA we select all rows where the [Column1] value (the pattern) is found in the beginning ("Text.StartsWith") of the current value in our outer loop ( "_" ) of QueryB. We could have used a function declaration for the outer loop too were it not for laziness.
This is akin to flipping a SQL query of Select * from [table] where [Value] like 'Pattern%' around to say Select * from [table] where 'Pattern%' like [Value]
Since we only want to keep the list items that don't match the pattern we are asking the list to only return the items where the row count for the inner loop is zero.
Hope this helps - let me know if this approach works for your scenario - not sure about the performance.
Thanks!!!! Very clear 🙂
I am not too concerned about performance as the number of criteria are limited and the query is only refreshed weekly...
Now you gave me another idea 🙂
I have tried to flip the select and put a condition against it vs. the text in queryA, it works really well.
However, to bring this to the final result I am aiming for: is it possible to loop through the rows of query A and get a list by row to be added as a Custom column?
To explain:
My QueryA looks like
| Text |
| App |
| Pe |
| Ban |
| All Others |
So if the list coming from QueryB is {Apples, Zebras, Bananas, Veggies, Pears,Pea}
The result should look like:
| Text | Column list |
| App | {Apple} |
| Pe | {Pears,Pea} |
| Ban | {Bananas} |
| All Others | {Zebras,Veggies} |
So the "All others" is the list built from the previous formula, which is fantastic
and what is <>"All others" is the list with the condition where each Table.RowCount(...) <> 0
but applied at the row level, so I guess an extra loop is needed?
Thanks again for all your help!!!!!!
Kind regards
Valeria
...sorry forgot a piece...
what I did is that I added a custom column to lookp throught the rows, and the a condition as in:
if [Column1]="All others" then Select else *********
and here I am not good enough to adapt the solution you gave me at a row level. If I say
List.Select(Source, each Table.RowCount(Table.SelectRows(QueryA, (k)=> Text.StartsWith( _ ,k[Column1])))<>0)then naturally I get a list of all items in QueryB corresponding to the start of Column1...
how can I adapt the formula to be in the row context?
Thanks!!! 🙂
let QueryB = {"Apples", "Zebras", "Bananas", "Veggies", "Pears", "Pea"}, Source = #table({"Text"}, {{"App"}, {"Pe"}, {"Ban"}, {"All Others"}}), #"Added Custom" = Table.AddColumn( Source, "Column list", (m) => if m[Text] = "All Others" then List.Select( QueryB, each Table.RowCount(Table.SelectRows(Source, (k) => Text.StartsWith(_, k[Text]))) = 0 ) else List.Select(QueryB, each Text.StartsWith(_, m[Text])) ) in #"Added Custom"Hello! This works great, I don't know how to thank you!
I tried to fiddle with the first formula for 1 h yesterday, but I just could not get there. I hope one day I'll be able to freeform formula this way :-). In the meantime, I am progressing in the reading of Miguel Escobar's PowerQuery book - if you have any other suggestions for learning this I am all ears.
Thanks again!!!!!!! 🙂