Mapping a value based on list sequence

[xtro]

Hello all, 

I'm sorry if the subject is not clear, but I'll try to explain it here.

So, I have data containing branch city and function type every time a package gets scanned IN through logistics shipping. Here's the example data :

My goal is to identify function type "GR" as "GR FM" or "GR LM".

The logic is :

1. If a route has function type "GR.A FM", then all of the following function type "GR" should be "GR LM"
2. if a route has not function type "GR. A FM", then the first function type "GR" will be "GR FM" and the rest "GR" will be "GR LM"
3. Any other function will stay as is

Here's the expected result :

I tried to do it in my manually back and forth using reference and merge, but I think there's another simpler and cleaner solution using list function or something. Here's the link to my version : link 

Anyone have another solution?

Thank you

[v-jingzhang · Accepted solution]

Hi @xtro 

Here is my solution. Hope it helps. 

let
    Source = data,
    #"Added Custom" = Table.AddColumn(Source, "RouteSplit", each Text.Split([Route], "/")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Types", each List.Transform([RouteSplit], each Text.BeforeDelimiter(_, "_"))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "New Types", each 
        if List.Contains([Types], "GR.A FM") 
        then List.Transform([Types], each if _ = "GR" then "GR LM" else _) 
        else 
            let 
                _firstGR = List.PositionOf([Types], "GR") 
            in 
                if _firstGR = -1 
                then [Types] 
                else List.ReplaceValue(List.ReplaceRange([Types], _firstGR, 1, {"GR FM"}), "GR", "GR LM", Replacer.ReplaceValue)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Route Mapped", each Text.Combine(List.Transform(List.Zip({[New Types],List.Transform([RouteSplit], each Text.AfterDelimiter(_, "_"))}), each Text.Combine(_, "_")), "/")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"Index", "Route", "Route Mapped"})
in
    #"Removed Other Columns"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[xtro]

Wow, it works! List in power query really is powerful. Thank you for the help!

If you don't mind, can you explain this part ? @v-jingzhang 

I get the hang it's to define "GR FM" and "GR LM". But what's the purpose of inside "let" and "in"?

[v-jingzhang]

Hi @xtro 

You could refer to Let - PowerQuery M | Microsoft Learn

In this case, I defined a variable _firstGR to get the position of the first "GR" in [Types] list, then used it in the expression to return. It is used at more than one place, so I define it as a variable to avoid evalulating it multiple times. This is my habit. In fact, in your case, the "let ... in" is not a must. You may have a try that if you modify this part into below, it also works as expected. 

Best Regards,
Community Support Team _ Jing

[xtro]

Thank you for the explanation!🙏