Your file links to a source that we can't access. You can click on the filter button in the column header (similar to Excel) and select only the locations you want. No need for M-coding.

@Riny van Eekelen,

The issue is that the entitles column contains multiple.  It is possible that the records lists entities that are not on our list as well as those that are on the list.  For example see attached data.xlsx file:

Row 1

CA, Irvine [NV];TX, Fort Worth;CT, Mystic;MN, Mounds View [SHA]

This 4 has 4 entities that are NOT on list of 6, so this cell would appear blank in this field.

Row 18

CA, Irvine [CSF];CO, Louisville;CT, Mystic;FL, Jacksonville;MA, Littleton;TX, Fort Worth 

the items in that row that I highlighted RED would be deleted as they are not part of the list of 6. The result for the entities field would be:

CA, Irvine [CSF];CO, Louisville;MA, Littleton;TX, Fort Worth 

I want ALL records to be visible, none of them should be filtered out.  Perhaps that was my error in not explaining myself properly.  The goal is to remove the entities not on the list from the field, and only that.  Spme records will only show 1 entity and some will be blank, that is the expected result.

• Israel, Caesarea
• CA, Irvine [CSF]
• TX, Fort Worth
• CO, Louisville
• MA, Littleton
• France, Lyon

See if the attached file does what you need. It contains a rather straight-forward query. I haven't put any effort into making it look nice though.

By the way, note that TX, Forth Worth is on the list of 6. So it will be the only one remaining on row 1.

@Riny van Eekelen,

That is exactly what I was trying to create!  But I simply had no clue as to how to create a query such as this.  One final question, is there any way for me to TRANSFER these steps into my live workbook, or do I need to recreate them manually?  I haven't done anything like this one before, so I don't have a clue. I just found the export queries, but I realized I did not facotor in a VERY important part.  When the source data is run, it comes in a report that is in this format.  The attached just shows the headers, all of which are used in the final result.    Can you add this in, and then the "List of 6" you have in column E on your file could replace Applicable QMS Entities on the attached sheet.  And thanks so much!!!!

@Riny van Eekelen,

I was just attempting to walk thru the steps in your query so I could understand what you did.  I need to convert it to the format of the source data with those other columns I mentioned in my last post.  But as I looked at each step, I saw some errors.  Errors only showed on the following 4 steps (screenshots attached):

Merged Queries

Expanded ListOfSix

Filtered Rows

Group Rows

This is the third time I have tried to submit a reply here.  You can submit the whole file with the name tbl_Refreshed as long as it has the same number of columns.  Then make another table with the cities you want and call it Table2.  I have tried to attach a file with no success.  If you need the file, I'll try again.

let
Source = Excel.CurrentWorkbook(){[Name="tbl_Refreshed"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(Source,{"Applicable QMS Entities"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Updated List", each List.Accumulate(
Table2[Column1],
"",
(state, current) =>
if Text.Contains([Applicable QMS Entities], current)
then state & " " & current
else state))
in
#"Added Custom"

I tried to add the file here.

@Riny van Eekelen & @Cedric McKeever,

Thanks so much, I really appreciate all your help.  I just could not figure this out as it was not a "static" list so the replacement aspect was more difficult.

Was you problem solved?  If not what went wrong?

@Cedric McKeever,

Yes, problem has been solved.  I marked your post as "Answered Post".  Thanks again for all your help!!