Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
9 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
12 | |
11 | |
9 | |
6 | |
6 |