Eliminate lasts characters from field by list loop criteria in Power Query

[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

 

[sandy666]

maybe

let
    Source = Excel.CurrentWorkbook(){[Name="Product"]}[Content],
    Count = Table.AddColumn(Source, "SCount", each List.Count(Text.Split([Products],"-"))-1),
    IF = Table.AddColumn(Count, "Custom", each if [SCount] = 1 then Text.BeforeDelimiter([Products], "-") else [Products]),
    TSC = Table.SelectColumns(IF,{"Custom"})
in
    TSC

Custom
001080001
006046122
008020018
014112448
206210830A
944444444-220110142-1
944444444-220110348-2
944444444-220110728-2
944444444-220110804-4
944444444-220110804-5
944444444- 220120007-8
944444444- 220120038- 2
944444444-220120183-11
944444444- 220120183-12
944444444-220120183-13
944444444-220120335-2
944444444-220120335-3
944444444-220120335-4
944444444 M -220110007-10
DET-001030-01
DET-001032-01
DET-001035-01
DET-001036-01
PIECE-220110099-2

btw. use XL2BB to post representative source data and expected result, pictures are useless

 

[sandy666]

or if you absolutely want to use color list table

let
    Product = Excel.CurrentWorkbook(){[Name="Product"]}[Content],
    TAD = Table.AddColumn(Product, "TAD", each Text.AfterDelimiter([Products], "-", {0, RelativePosition.FromEnd}), type text),
    Color = Excel.CurrentWorkbook(){[Name="Color"]}[Content],
    Join = Table.NestedJoin(TAD,{"TAD"},Color,{"color list"},"Color",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "Color", {"color list"}, {"color list"}),
    Replace = Table.ReplaceValue(Expand,null,"@",Replacer.ReplaceValue,{"color list"}),
    IF = Table.AddColumn(Replace, "Result", each if not Text.Contains([Products], [color list]) then [Products] else Text.BeforeDelimiter([Products], "-")),
    TSC = Table.SelectColumns(IF,{"Result"})
in
    TSC