Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Post Partisan
3 years ago
Solved

Create a list from one column excluding values from another

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

Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

     

     

    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"

     

     

9 Replies

  • ValeriaBreve's avatar
    ValeriaBreve
    Post Partisan
    3 years ago

    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's avatar
      lbendlin
      Super User
      3 years ago

      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's avatar
        ValeriaBreve
        Post Partisan
        3 years ago

        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:

         

        TextColumn 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