changing a column with numeric codes into text based values from different table

In power query right click on the company column, select split column > by delimeter 

- Ensure ";" semi-colon is the delimeter

- Ensure "Each occurence of the delimeter" 

- Ensure advanced options set to 'split into rows'

* you may have to include an extra step to deal with any spaces between delimiter *

Now you will have a row for each country code that you can use to join

Hello, ajohnso2 ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hi,monikat .I am glad to help you.
Using the merged query can indeed achieve your needs, I carried out the following tests (based on the advice provided by ajohnso2 ), and successfully achieved the results you want, I hope that my following tests will be helpful to you.
This is my test data:

Below is my result:

The data in the Company column was first split, and then each column was queried jointly with the data in table1

The results are then merged (multiple columns into one)

You can remove redundant columns

Here is my test M code:

let
    Source = Excel.Workbook(File.Contents("C:\Users\username\Desktop\test11_21.xlsx"), null, true),
    List_Sheet = Source{[Item="List",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(List_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Cocument Status", type text}, {"Company", type any}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Company", type text}}, "en-US"), "Company", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Company.1", "Company.2", "Company.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Company.1", Int64.Type}, {"Company.2", Int64.Type}, {"Company.3", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Company.1"}, Table1, {"Company Codes(PCM)"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Except Duty Free"}, {"Table1.Except Duty Free"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Table1", {"Company.2"}, Table1, {"Company Codes(PCM)"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries1", "Table1", {"Except Duty Free"}, {"Table1.Except Duty Free.1"}),
    #"Merged Queries2" = Table.NestedJoin(#"Expanded Table2", {"Company.3"}, Table1, {"Company Codes(PCM)"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(#"Merged Queries2", "Table1", {"Except Duty Free"}, {"Table1.Except Duty Free.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Expanded Table3",{"Table1.Except Duty Free.2", "Table1.Except Duty Free", "Table1.Except Duty Free.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Company.1", "Company.2", "Company.3"})
in
    #"Removed Columns"

I have also shared the pbix file for the test, hope that helps.

You can mark ajohnso2's suggestion as a solution that will help more people.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table28"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Amount", type number}, {"ID Category", type text}}),
    Custom1 = Table.ToRows(Table29),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.ReplaceMatchingItems(Text.Split([ID Category],","),Custom1),", ")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ID Category"})
in
    #"Removed Columns"

Table 1 is Table28 and Table 2 is Table29.

Hope this helps.