Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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.
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
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.
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.
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.
@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.
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.
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |