Eliminate lasts characters from field by list loop criteria

pepito · Dec 21, 2020

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 · Dec 21, 2020

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 · Dec 22, 2020

I have now seen the google link.

1st Query: Named Color_list

Code:

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

2nd Query:

Code:

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 · Dec 22, 2020

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 · Dec 22, 2020

Now, your good solution, works in all cases!

Eliminate lasts characters from field by list loop criteria

pepito

New member

pinarello

Member

pinarello

Member

p45cal

Super Moderator

Attachments

pinarello

Member

Attachments