Help with Power Query to extract only matching codes from reference codes

[Arun Chandramouli]

Hi All,

Hope you are doing well!.. I am trying to extract the matching codes from the column diagcode in my input data with respect to the list of reference diag codes.. The output diag codes are the ones extracted from the column diagcode with respect to the matching reference diag codes...Can you please help me here...



Thanks,
Arun

[#2]

@Arun Chandramouli 

That could be

let

    Source = Excel.CurrentWorkbook(),
    Ref = Source{[Name="Reference"]}[Content],
    Reference = Table.TransformColumns(
        Ref,
        {{"Reference diag code", Text.Trim, type text}}),

    Table = Source{[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Table,{"output diag code"}),
    Output = Table.AddColumn(
        #"Removed Columns",
        "allcodes",
        each
            Text.Combine(
                List.Intersect(
                    {
                        List.Transform( Text.Split( [diagcode], ","), each Text.Trim(_) ),
                        Reference[Reference diag code] }), ","
            )
    )
in
    Output

[#3]

@Arun Chandramouli Too late to beat @Sergei Baklan , but as a variant, though less robust solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    RefCode = Source2[Reference diag code],

    Split = Table.AddColumn(Source, "Custom", each Text.Split ([diagcode], ",")),
    Match = Table.AddColumn(Split, "Output", each List.Intersect ({[Custom], RefCode} )),
    Extract = Table.TransformColumns(Match, {"Output", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(Extract,{"Custom"})
in
    #"Removed Columns"

[#4]

@Arun Chandramouli 

Yes, @Riny_van_Eekelen solution, if practically the same, is more easy in maintenance and debugging. The only I'd add Text.Trim on codes. Based on samples extra spaces are possible.