Forum Discussion
Post PartisanCreate 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"
Post PartisanThanks!!!! 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
Post Partisan...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!!! 🙂
- lbendlin
Super User
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"- ValeriaBreve
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!!!!!!! 🙂
- lbendlin
I would recommend you inhale Ben Gribaudo's M primer. It may be tough reading at times but absolutely worth it.