Create a list from one column excluding values from another

[ValeriaBreve]

Hello!

I have a query (QueryA) with a text column.

I am collecting a list of text strings from another query (QueryB), and from this list I need to exclude what starts with any of the text in the column of QueryA.

So in practice:

QueryA:

Column1

Art

App

B

Cat

E

List: List.Distinct(QueryB[MyColumn])

say it contains {Apples, Zebras, Bananas, Veggies}

then the result should be {Zebras, Veggies})

How can I do it? I can't find the right expression....

Thanks!

Kind regards

Valeria

[lbendlin · Accepted solution]

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 ...  ?

let
    Source = {"Apples", "Zebras", "Bananas", "Veggies"},
    Select = List.Select(Source, each Table.RowCount(Table.SelectRows(QueryA, (k)=> Text.StartsWith( _ ,k[Column1])))=0)
in
    Select

[ValeriaBreve]

Hello!

Thank you so much! It works spotlessly! 🙂 I actually did not know that Chris Webb had blogged about it - but I read it now and found it very interesting, thanks! My need is actually a business need. I have a report with a high number of queries (almost 90) all coming from different Excels that need to be sliced according to business criteria that might change over time... 

So instead of hard coding the criteria in M I had the users build Ecxel tables, so that when the crieria change the PowerBI refresh will do its magic 🙂 

But now knowing M well enough, well, that's hard! I have started to learn but formula free forming is still very hard for me.

May I impose and ask specifically about the formula you used?

So List.Select is to only keeps specific items in the list that fill the criteria 

I tried to figure out the rest of the formula, I can more or less understand it but not to the specifics - could you please walk me through it step by step? So I can learn from it 🙂

Thanks again!

Kind regards

Valeria

[lbendlin]

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.

[ValeriaBreve]

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

[ValeriaBreve]

...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 · Accepted solution]

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.

[ValeriaBreve]

Thanks! I was never pointed to this resource before - I will definitely read it!!!! 🙂

[lbendlin · Accepted solution]

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 ...  ?

let
    Source = {"Apples", "Zebras", "Bananas", "Veggies"},
    Select = List.Select(Source, each Table.RowCount(Table.SelectRows(QueryA, (k)=> Text.StartsWith( _ ,k[Column1])))=0)
in
    Select