Results 1 to 5 of 5

Thread: Eliminate lasts characters from field by list loop criteria

  1. #1
    Neophyte pepito's Avatar
    Join Date
    Dec 2020
    Posts
    1
    Articles
    0
    Excel Version
    2016

    Eliminate lasts characters from field by list loop criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 )
    Click image for larger version. 

Name:	Sample.jpg 
Views:	143 
Size:	77.3 KB 
ID:	10241

    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

  2. #2
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    129
    Articles
    0
    Excel Version
    Office 365
    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.

  3. #3
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    129
    Articles
    0
    Excel Version
    Office 365
    I have now seen the google link.

    1st Query: Named Color_list
    PHP Code:
    let
        Source 
    Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"color_list", type text}})
    in
        
    #"Changed Type" 
    2nd Query:
    PHP 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" 

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,974
    Articles
    0
    Excel Version
    365
    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.
    Attached Files Attached Files

  5. #5
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    129
    Articles
    0
    Excel Version
    Office 365
    Now, your good solution, works in all cases!
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •