Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DebbieE
Community Champion
Community Champion

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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
Smauro
Solution Sage
Solution Sage

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

v-lionel-msft
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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

DebbieE
Community Champion
Community Champion

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!!

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

DebbieE
Community Champion
Community Champion

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?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

DebbieE
Community Champion
Community Champion

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. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.