Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I need to verify if each row on column "ID" starts with any value from a list from the column "ID starts with" in the same table.
This list will be different for each "Group ID".
I am trying the following but gives an error (Expression.Error: We cannot convert a value of type List to type Text.)
List.AnyTrue( List.Transform({Text.SplitAny([ID Starts With], ",")}, (substring) => Text.Contains([ID], substring)))
I have to do this on the Power Query editor.
Example:
"Group ID" "ID" "ID Starts with" "Result expected"
A TRP123 TRP, PT Yes
A RUN123 TRP, PT No
B OL123 RUN No
C PT123 TR, RUN No
C TR123 TR, RUN Yes
D ABC123 ABC , RUN, PT, TR Yes
Appreciated for your help.
Solved! Go to Solution.
NewStep=Table.AddColumn(PreviousStepName,"Result Expected",each List.Contains(Text.Split([ID Starts with],","),[ID],(x,y)=>Text.StartsWith(y,x)))
NewStep=Table.AddColumn(PreviousStepName,"Result Expected",each List.Contains(Text.Split([ID Starts with],","),[ID],(x,y)=>Text.StartsWith(y,x)))
This approach worked for me too, thanks for posting.
Can you explain the last part of the step? The "[ID],(x,y)=>Text.StartsWith(y,x)))" part? I've never seen the => in a step, and i dont understand why the x,y before the => switches to y,x (flips order). I assume the x, y are variables?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.