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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
MarcioLeal89
Advocate I
Advocate I

Does ID starts with any value from list from column in the same table

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.

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

NewStep=Table.AddColumn(PreviousStepName,"Result Expected",each List.Contains(Text.Split([ID Starts with],","),[ID],(x,y)=>Text.StartsWith(y,x)))

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

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?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors