Power Bi extract strings from column based on a code list

Question

I have a column of drawing numbers and a list of codes. I want to see if any of the drawing numbers contain any codes, I just can't seem to find a way to compare the list and extract these codes.

If anyone can point me in the right direction that would be really helpful.

This ? stackoverflow.com/questions/73000061/… If not post input and desired output samples — horseyride
– horseyride, Commented Jul 19, 2022 at 9:35

Mik · Accepted Answer · 2022-07-19 10:59:14Z

0

This is an idea how to get a table with columns drawing no. - list of codes. Hope, this will help, if not add a data sample and a result sample.

VAR drawings=VALUES(myTbl[drawing])

VAR drawingsAndCodes=
        ADDCOLUMNS(
            drawings
            ,"codes",VAR dr=[drawing] 
                     VAR codes=VALUES(myTbl[code])
                        RETURN CONCATENATEX(
                            FILTER(
                                codes
                                ,Search([code],dr,1,0)>0),[code],";"))
  
  
RETURN
    drawingsAndCodes

The Measure for matrix

VAR dr=SELECTEDVALUE(myTbl[drawing]) 
VAR codes=VALUES(myTbl[code])
RETURN CONCATENATEX(
                        FILTER(
                            codes
                            ,Search([code],dr,1,0)>0),[code],";"))

edited Jul 19, 2022 at 10:59

answered Jul 19, 2022 at 10:02

Mik

2,1011 gold badge7 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

adam Wadsworth Over a year ago

i get an error The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Mik Over a year ago

It returns a table, not a single value. Add a result sample, this will help to get a right output.

Mik Over a year ago

Do you want to put the measure into a matrix?

Mik Over a year ago

Do you have any linkage for columns?

Mik Over a year ago

I added the measure for matrix

|

Collectives™ on Stack Overflow

Power Bi extract strings from column based on a code list

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related