Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Mapping a value based on list sequence

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 logis...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi Anonymous 

     

    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.

    mapping based on sequence in a list - Copy.xlsx26 KB
v-jingzhang's avatar
v-jingzhang
Community Support
3 years ago

Hi Anonymous 

 

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.

mapping based on sequence in a list - Copy.xlsx26 KB
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    v-jingzhang
    Community Support
    3 years ago

    Hi Anonymous 

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you for the explanation!🙏