Extract text from string using list

[cottrera]

Hi I have a table in power bi called Post_Inspections. This table has a free text column call Post Inspect notes that cantains comments and codes. See example below.

Post_Inspections table

Post Inspect Notes

Spoke to tenant who conffirmedd that the isssue had been resolved to her satisfaction.  Resolved DMRSV.  Cause CAAMC

Spoke to tenant all plastering works completed to a satisfactory standard.  No further issues resolved = DMRV  Other = CAOTH

Spoke to tenant who confirmed that the works have been carried out andd there are no further issuess.    Resolved = DMRSV   cause = CAAMC

Spoke to tenant confirmed there were no further issues.  Resolved  = DMRSV Cause = CAAMC

Spoke to tenant who confirmed that all works have now been completed and mould issues have now been resolved. DMRSV  CAAMC

Spoke to customer who confirmed all works completed and the issues have been resolved =DMRV.  Cause was CAAMC

Spoke to tenant who confirmed that the mould issues had been resolved.  DMRSV   CAAMC

Spoke to tenant today who conirme that issue with mould had been resolved satisfactorily DMRSV. Cause CAAMC

Spoke to tenant who confirmed issue had been resolved satisfactorily.  Resolved DMRSV  CAAMC

Spoke to tenant who confirmed all issues had been resolved and the mould has not returned.  DMRSV CAAMC

Spoke to tenant today who confirmed that the mould issues have been resolved appropriately.  DMRSV    CAAMC

Spoke to tenant who confirmed that the works had been completed and that the mould had not returned.  DMRSV   CAAMC

Spoke to tenant who confirmed that the issue with mould had now been resolved.  DMRSV   CAAMC

Spoke to tenant who confirmed that the works have been carried out and the mould issues have been resolved. DMRSV   CAAMC

I also have a table called  codes table which contains a list of codes thatb I wish to extract

Codes Table

Code

CAAMC

CAOTH

CASER

CAURD

CAURL

CAWAL

DMESC

DMFUR

DMFWR

DMNEWDM

DMRSV

Is it possible to use ower query to extract the codes from the Post Inspections table , if they appear on the seperate Codes Table  and display these codes seperately in a new column in the Post Inspections table

thank you

Richard

[cottrera]

Thank you Smauro.  I tried again this morning and your code works fine 😀

[cottrera]

Thank you for your quick response. I am still quite a novice with Mcode. So I am struggling to add your line of code to a new step.  Below are my transformations that are against the posy inspections table. Are you able to show me how to succesfullty as your peice of code thank you RIchard

let
Source = Sql.Database("blahblahblah"),
Repairs_Job_PostInspections = Source{[Schema="Repairs",Item="Job_PostInspections"]}[Data],
#"Renamed Columns" = Table.RenameColumns(Repairs_Job_PostInspections,{{"Inspector", "Post Inspector#"}}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Repairs_Job_Id", "Post Inspector#", "Inspection_Date", "Inspection_Notes", "Inspection_Satisfactory YN", "Inspection_Status"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Inspection_Date] > #date(2022, 4, 1)),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Repairs_Job_Id"}, #"Inspections job# only", {"Job#"}, "Inspections job# only", JoinKind.LeftOuter),
#"Expanded Inspections job# only" = Table.ExpandTableColumn(#"Merged Queries", "Inspections job# only", {"Supplier Name"}, {"Supplier Name"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Inspections job# only", each ([Supplier Name] = "Asset Management - Inspections")),
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows1"),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Duplicates",{{"Inspection_Date", "Post Inspect_Date"}, {"Inspection_Satisfactory YN", "Post Inspect Satisfactory"}, {"Inspection_Notes", "Post Inspect Notes"}}),
#"Merged Queries1" = Table.NestedJoin(#"Renamed Columns1", {"Post Inspector#"}, Operatives, {"Operative"}, "Operatives", JoinKind.LeftOuter),
#"Expanded Operatives" = Table.ExpandTableColumn(#"Merged Queries1", "Operatives", {"Operative Name"}, {"Operative Name"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded Operatives",{{"Operative Name", "Post Inspected By"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns2", "Post Inspect Notes", "Post Inspect Notes - Copy"),
#"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Repairs_Job_Id", "Post Inspector#", "Post Inspect_Date", "Post Inspect Notes", "Post Inspect Satisfactory", "Inspection_Status", "Supplier Name", "Post Inspect Notes - Copy", "Post Inspected By"})
in
#"Reordered Columns"

[Smauro · Accepted solution]

Yeah, it's possible. You could add this step:

=
    let
        codeList = Codes[Code]
    in
        Table.AddColumn(
            PreviousStep,
            "Codes In Notes",
            each
                Text.Combine(
                    List.Select(
                        codeList,
                        (c) => Text.Contains([Post Inspect Notes], c)
                    ),
                    ","
                ),
            type text
        )

where PreviousStep is your previous step's name in Power Query.

(Assuming 'Codes' Table is actually named Codes and its column Code)

How that works is it reads the Code list once, and then filters it for each row for whatever code is found inside the notes. Last, it combines it to text in order to keep the result in one row.

Now, if you then need to filter, then you could leave the list as is, take the whole thing to a new table, expand it to new rows and then relate both. Or you could add a true/false column for each code automatically. It's all dependent on what you'd like to do with that result.

The result should look like this:

Cheers,