none
Value must contain one of the value from the list RRS feed

Answers

  • Hi THELAP,

    anthony's second solution should work for you.

    But if your list is actually very long, this solution might be faster to implement. Just add a column with this formula:

    List.AnyTrue(List.Transform({"Peter", "Jacob", "Thomas"}, (l)=> Text.Contains([Column 2], l, Comparer.OrdinalIgnoreCase)))


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, February 28, 2019 5:56 PM
    Moderator
  • Actually I am not sure what you want to do.

    If you want to add a column "Calculated Column" showing true if [Column 2] contains on of the name, then try this:

    Table.AddColumn(#Your Table", "Calculated Column", each
         Text.Contains(Text.Lower([Column 2]),"peter") or 
         Text.Contains(Text.Lower([Column 2]),"jacobs") or 
         Text.Contains(Text.Lower([Column 2]),"thomas")
    )

    Thursday, February 28, 2019 11:41 AM

All replies

  • Table.SelectRows( #"your Table", each List.Contains({"Peter","Jacobs","Thomas"}, [Column 2]) )

    Or if you want to manage the Case:

    Table.SelectRows( #"your Table", each List.Contains({"peter","jacobs","thomas"}, Text.Lower( [Column 2])) )
    
    
    

    Thursday, February 28, 2019 11:36 AM
  • Actually I am not sure what you want to do.

    If you want to add a column "Calculated Column" showing true if [Column 2] contains on of the name, then try this:

    Table.AddColumn(#Your Table", "Calculated Column", each
         Text.Contains(Text.Lower([Column 2]),"peter") or 
         Text.Contains(Text.Lower([Column 2]),"jacobs") or 
         Text.Contains(Text.Lower([Column 2]),"thomas")
    )

    Thursday, February 28, 2019 11:41 AM
  • Thanks for your quick reply.

    What I want is to make a New Column called "Calculated Column". I tried your suggestion without lock :-(

    I am new in power BI, maybe is another way to get what I want to achive then making new column?

    Thursday, February 28, 2019 12:41 PM
  • Hi THELAP,

    anthony's second solution should work for you.

    But if your list is actually very long, this solution might be faster to implement. Just add a column with this formula:

    List.AnyTrue(List.Transform({"Peter", "Jacob", "Thomas"}, (l)=> Text.Contains([Column 2], l, Comparer.OrdinalIgnoreCase)))


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, February 28, 2019 5:56 PM
    Moderator
  • I tried to make a new column and use the formula you have sent but both of your suggestion give me a error massage (the syntax for "Table" ...). see pic below. What do I do wrong?

    Friday, March 1, 2019 8:56 AM
  • As you've posted your question in the Power Query - forum, you got solutions that work in the Query Editor (M-language) and not in the data model (DAX-language).

    Go to "Edit Queries" and add a column there.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, March 1, 2019 8:59 AM
    Moderator
  • Thank you for your quick reply.  I want to do it in my data model. Do you have a solution for that?
    Friday, March 1, 2019 9:37 AM
  • No, I don't. I recommend you post your question in the Power Pivot-Forum instead.

    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, March 1, 2019 10:46 AM
    Moderator
  • No, I don't. I recommend you post your question in the Power Pivot-Forum instead.

    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Hi THELAP,

    From a Power Query point of view, it looks like this thread has one or more working solutions. 

    I can see that you've posted in the Power Pivot forum as advised by Imke.  Some DAX solutions have also been provided in your Power Pivot thread by one other member of the Power Pivot forum and myself:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1dc34dad-7b24-41e3-aa79-015f7c7c8226/value-must-contain-one-of-the-value-from-the-list?forum=sqlkjpowerpivotforexcel


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Friday, March 8, 2019 7:40 AM
    Moderator
  • Does this formula work with DirectQuery too?
    Tuesday, April 2, 2019 8:55 AM