selecting rows contain text from list and record list value.

[acbakker]

My current code is:

let
    Source = #"CAL multiple action",
    #"Renamed Columns" = Table.RenameColumns(Source,{{"CALIBRATIONS_Omschrijving:", "Column1"}}),

    LookUp = #"ID_Unique",

    #"Filtered Rows" = 
        Table.SelectRows(
            #"Renamed Columns", 
            (r)=> List.AnyTrue(
                    List.Transform(
                        LookUp[Column1], 
                        each Text.Contains(r[Column1], _, Comparer.OrdinalIgnoreCase)
                        )
                )
        )
in
    #"Filtered Rows"

This will generate the table with rows containing items from the lookup list. Now I also like to now what value is found.

Any help is highly appreciated. (As you can see I'm not familiar with M-language but try to find my way around 😉 ).

Thanks Christiaan

Table 1

ID-nummer:CountCALIBRATIONS.File NameCALIBRATIONS.ID-nummer:CALIBRATIONS_Omschrijving:CALIBRATIONS.Opname element:CALIBRATIONS.Model/type:CALIBRATIONS.Fysieke range:CALIBRATIONS.Interval:CALIBRATIONS.Testpunt(en)CALIBRATIONS.en tolerantie(s):CALIBRATIONS.Column1CALIBRATIONS.Column2CALIBRATIONS.Locatie:CALIBRATIONS.Fabrikant:CALIBRATIONS.Serie nr:CALIBRATIONS.Afleesnauwkeurigheid:CALIBRATIONS.Gebruikte range: CALIBRATIONS.Column3CALIBRATIONS.Procedure:CALIBRATIONS.Test standaard:

PM-9540

36

PM-9540,25NewCSF.xls

PM-9540

Temp. of -70 Freezer UK-7265

Pt-100

NA

-200°C tot 400°C

12 maanden

≤ -80°C ± 3°C

≥ -50°C ± 3°C

H-02

Yokogawa

0.1°C

-80 t/m -50°C

WV00405

Temperatuur standaard

PM-9540

36

PM-9540,24NewCSF.xls

PM-9540

Temp. of Incubator KV-0180

Pt-100

NA

-200°C tot 400°C

12 maanden

≤ 23°C ± 0.5°C

≥ 42°C ± 0.5°C

H-02

Yokogawa

0.1°C

23 - 42°C

WV00405

Temperatuur standaard

PM-9540

36

PM-9540,39NewCSF.xls

PM-9540

%RH probe of Incubator KV-0250

NA

NA

55 - 80 %RH

12 maanden

≤55  %RH ± 2 %RH

≥80 %RH ± 2 %RH

H-02

Yokogawa

0.1 %RH

70 - 85 %RH

AL00094

%RH standaard

PM-9540

36

PM-9540,26NewCSF.xls

PM-9540

Temp. of -70 Freezer UK-7270

Pt-100

NA

-200°C tot 400°C

12 maanden

≤ -80°C ± 3°C

≥ -50°C ± 3°C

H-02

Yokogawa

0.1°C

-80 t/m -50°C

WV00405

Temperatuur standaard

PM-9540

36

PM-9540,23NewCSF.xls

PM-9540

Temp. of Refrigerator KK-1435

Pt-100

NA

-200°C tot 400°C

12 maanden

≤ 2°C ± 0.5°C

≥ 16°C ± 0.5°C

H-02

Yokogawa

03-2012-41210002-200-03

0.1°C

2 - 16°C

WV00405

Temperatuur standaard

Table 2

BM-0206

BM-0208

BM-0209

BM-0210

BM-0216

BM-0217

BM-0222

BM-0223

BM-0224

BM-0225

BM-0226

CALIBRATION.Omschrijving (4th column of table 1) contains codes from Table 2. I managed the selections of thw rows. But I like to know the values from Table 2 instead. 

[PattemManohar]

@acbakker Could you please post sample data and expected output which will help us to understand what you are trying to achieve.

[acbakker]

@PattemManohar

I have updated the post. Instead of heving the CALIBRATION>omschrijving selected with the codes in it I would like to know which once are used in that column.

Thanks for your support.

[v-lili6-msft]

hi, @acbakker

Your description of table 1 is a bit confusing, could you please rearrange it again and give us your  expected output?

Best Regards,

Lin