Eliminate lasts characters from field by list loop criteria

[pepito]

Hi guys, hope you're doing fine:
I have a table in Excel with many products, these products may have a color id by the end of the product code chain or not.
I have another table with colors ( let's say a color list )


How can I do a loop to eliminate color id by row in Products based in color list? I have tried to go with last occurrence of delimitator '-' but my rows may have other numbers than color ids by the end
Thank you very much in advance

**List of colors in very short for sample purposes

Here's Excel sample file:
https://drive.google.com/file/d/1no3vLxcqQOuhQp1fga_Pd4SKwDgas9UD/view?usp=sharing

 

[pinarello]

Power Query is really very powerful. But image as source is not possible yet. Therefore please upload a sample folder.

Alternatively, I would proceed as follows:

> Load color table as connection.
> load material list
> duplicate material number
> split duplicated material number from right one at "-".
> Perform a merge with the right result of the split using the color table.
> Insert user-defined field: If merge was successful, then use left split field as new material number, if not use original field as new material number.

 

[pinarello]

I have now seen the google link.

1st Query: Named Color_list

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"color_list", type text}})
in
    #"Changed Type"

2nd Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Products", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Products", "Products2"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Products2", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"Products2.1", "Products2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Products2.1", type text}, {"Products2.2", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Products2.2"}, Color_list, {"color_list"}, "Color_list", JoinKind.LeftOuter),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "Color_list", {"color_list"}, {"color_list.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded {0}", "Product code", each if [color_list.1] = null then [Products] else [Products2.1]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Product code"})
in
    #"Removed Other Columns"

 

[p45cal]

Another, see attached, column H
It will also remove instances in the middle of the string (but not at the beginning) see rows 13 & 14.

 

[pinarello]

Now, your good solution, works in all cases!