cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User I
Super User I

Conditional Column in M using another table as a reference

conditional Column referencing a List.JPG

Basically, If this description column contains anything in a seperate reference table I have e.g.

 

Reference Lookup table

TYE

COP

HAE

NOP 

WEP

 

Then True Else False

 

So basically I dont want to hard code these in, I want them to come from this reference table. Is there any way I can do that with the Conditional Column?

1 ACCEPTED SOLUTION

@DebbieE see attached, there are two tables, "search and replace" table is the one used for dynamic replace, it will search the text in "search" column (regardless of length) and replace the value from "replace" column

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

11 REPLIES 11
Super User I
Super User I

Hi @DebbieE 

 

@parry2k's solution is great if you also want to do some replacements.

If you'd only like a true/false output, then you could add a function on your code directly and then call it while adding a new column. It will return true as soon as it finds a value from the lookup or false if it goes through all of them and finds nothing:

    fnSearch =
        let search = (t as text, l as list, x as logical) =>
            if List.Count(l) > 0 and x = false then
                @search(t, List.Skip(l, 1), Text.Contains(t, l{0}, Comparer.OrdinalIgnoreCase))
            else x
        in search,
    col = Table.AddColumn(PreviousStep, "search", each fnSearch([data], #"ref table"[Column], false), type logical)

Where [data] is the column you want to search on and #"ref table"[Column] is your reference lookup table's column containing the values you wish to search dynamically.

 

It is not case sensitive: if you want case sestitivity you should remove the Comparer.OrdinalIgnoreCase.

 

Cheers




Feel free to connect with me:
LinkedIn

Community Support
Community Support

Hi @DebbieE ,

 

Try to use DAX to create a calculated column, you can use IF() or SWITCH() function.

 

Column = 
IF(
    table[column] in {"TYE", "COP", "HAE" ,"NOP", "WEP"},
    TRUE(),
    FALSE() 
)

 

Or you can do like this.

aaa2.PNG

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

The hardcoded version is exactly what im trying to avoid doing

@DebbieE see attached, there are two tables, "search and replace" table is the one used for dynamic replace, it will search the text in "search" column (regardless of length) and replace the value from "replace" column

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

thank you so much. 

 

I will block some time out on monday to go through it

@DebbieE sounds like a plan, if you have any questions, please let me know and I will be more than happy to explain, although everything is pretty much self-explanatory. 🙂

 

Have a great weekend!!

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Super User III
Super User III

@DebbieE yes it can be done but the solution depends if the value you want to search is at a fixed location in the text or it can be anywhere in the text. I guess the length of 3 characters is fixed?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Yes, it can be anywhere in the text. Im looking for Specific Values from the reference table. Its a possibility they are all length of 3 but that may not always be the case. i dont want to hard code them because they can change

@DebbieE ok sounds good. Let me try to put together something, not sure where to start but it will be something interesting to solve, I think it is much easier in DAX. Is that an option?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





I could do it in DAX but I have lots of complext logic that Im setting as flags in order to make a column at the end and it seems cleaner to do it all in M so you can follow the logic

@DebbieE seems like I figured it out, just stress testing and polishing the solution, stay tuned. 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors