Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
I'm using the following to check a list of products in a string against a list from a table:
if List.ContainsAny(Text.Split([ProductsList],"|"),#"ExceptionProducts"[Product]) then 1 else 0
This works perfectly, returning 1 for a match and 0 for no match. But how can I return the value that was matched?
for example:
I have a row, where ProductsList contains the following "ABA|ABB|ABC|ABD|ABE|ABF"
And my ExceptionList contains {ABD,ABJ,ABZ}
I would like to return the value ABD instead of 1.
It's unlikely there will be more than 1 match, but if it's possible to just return the first?
IS this even possible?
Solved! Go to Solution.
try this
let
Source = tab,
l2={"ABD","ABJ","ABF"},
#"Added Custom1" = Table.AddColumn(Source, "Exception", each let l1=Text.Split([ProductList],"|"),p=List.PositionOfAny(l1,l2,Occurrence.First) in try l1{p} otherwise null)
in
#"Added Custom1"
another way, may be cleaner, could be this which use a user defined function:
let
Source = tab,
l2={"ABD","ABJ","ABF"},
#"Added Custom1" = Table.AddColumn(Source, "Exception", each extract_exception([ProductList],l2))
in
#"Added Custom1"
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create a custom column and input the following codes.
let x =
List.Intersect(
{
Text.Split([ProductsList],"|"),
#"Changed Type"[Exceptionlist]
}
)
in
if
List.Count(x)>0
then x{0} else ""
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hello
do you know how to adjust your formula if the exeptionlist is in the other table/source?
AnotherSouce[Exceptionlist] does not work here
thank you in advance!
give a try to this
let
l1=Text.Split("ABA|ABB|ABC|ABD|ABE|ABF","|"),
l2={"ABD","ABJ","ABF"}
in List.Transform(List.PositionOfAny(l1,l2,Occurrence.All), each l1{_})
if you need only one element change occurrence.all with occurrence.first, for example
@Anonymous Hi. Thanks for this, I think this does what I want. BUT, I'm trying to convert your line
in List.Transform(List.PositionOfAny(l1,l2,Occurrence.All), each l1{_})
into an added collumn, by passing in my data as follows:
Let
Source = My Connection to the data
l1 = Text.Split([ProductList],"|"),
l2 = #"Exceptions"[ExceptionProducts],
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Exception", each List.Transform(List.PositionOfAny(l1,l2,Occurrence.First), each l1{_}))
in
#"Added Custom1"
What am I doing wrong?
try this
let
Source = tab,
l2={"ABD","ABJ","ABF"},
#"Added Custom1" = Table.AddColumn(Source, "Exception", each let l1=Text.Split([ProductList],"|"),p=List.PositionOfAny(l1,l2,Occurrence.First) in try l1{p} otherwise null)
in
#"Added Custom1"
another way, may be cleaner, could be this which use a user defined function:
let
Source = tab,
l2={"ABD","ABJ","ABF"},
#"Added Custom1" = Table.AddColumn(Source, "Exception", each extract_exception([ProductList],l2))
in
#"Added Custom1"
@Anonymous Great solutions. Thanks for your help
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.