Forum Discussion

inawab's avatar
inawab
Frequent Visitor
3 years ago

An auto custom column

Hi everyone,

 

I am not sure if this can be done, I have below column

 

I Split Column by Delimiter "/" 

 

there will be up to 21 new column, now I need to join every 2 column with the below condition 

 

if [Routing.2] = [Routing.3] then [Routing.3] &"-"&[Routing.4] else

if [Routing.2] <> null and [Routing.3] <> null then [Routing.2]&"-"&[Routing.3] else null

 

and I must repeat this up to twenty-one time each time with the next columns, so the next one will be:

 

if [Routing.3] = [Routing.4] then [Routing.4] &"-"&[Routing.5] else

if [Routing.3] <> null and [Routing.4] <> null then [Routing.3]&"-"&[Routing.4] else null

 

there will be times when I will have more than twenty-one columns after the Splitting, therefore I need to rebate above formula more than twenty-one time.

 

My question can we auto this process so it can be adjusted to any changes happen in the future? 

Need Help

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi inawab ,

     

    Can you supply your original example table in a copyable format please?

    Best to copy that column in Power Query, paste it into Home tab > Enter Data, then copy and paste the Advanced Editor code from that into a code window ( </> button ) here.

     

    Please also provide an example of what your expected output looks like for a few rows of the original table.

     

    Pete

    • inawab's avatar
      inawab
      Frequent Visitor
      3 years ago

      this up to 4 repeats

       

          #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value5", "Routing", Splitter.SplitTextByDelimiter("/", QuoteStyle.None), {"Routing.1", "Routing.2", "Routing.3", "Routing.4", "Routing.5", "Routing.6", "Routing.7", "Routing.8", "Routing.9", "Routing.10", "Routing.11", "Routing.12", "Routing.13", "Routing.14", "Routing.15", "Routing.16", "Routing.17", "Routing.18", "Routing.19", "Routing.20", "Routing.21"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Routing.1", type text}, {"Routing.2", type text}, {"Routing.3", type text}, {"Routing.4", type text}, {"Routing.5", type text}, {"Routing.6", type text}, {"Routing.7", type text}, {"Routing.8", type text}, {"Routing.9", type text}, {"Routing.10", type text}, {"Routing.11", type text}, {"Routing.12", type text}, {"Routing.13", type text}, {"Routing.14", type text}, {"Routing.15", type text}, {"Routing.16", type text}, {"Routing.17", type text}, {"Routing.18", type text}, {"Routing.19", type text}, {"Routing.20", type text}, {"Routing.21", type text}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"PAX Count", 0}}),
    Leg1 = Table.AddColumn(#"Replaced Errors", "Leg1", each [Routing.1] &"-"&[Routing.2]),
    Leg2 = Table.AddColumn(Leg1, "Leg2", each if [Routing.2] = [Routing.3] then [Routing.3] &"-"&[Routing.4] else

if  [Routing.2] <> null and [Routing.3] <> null then [Routing.2]&"-"&[Routing.3] else null),
    Leg3 = Table.AddColumn(Leg2, "Leg3", each if [Routing.3] = [Routing.4] then [Routing.4] &"-"&[Routing.5] else

if  [Routing.3] <> null and [Routing.4] <> null then [Routing.3]&"-"&[Routing.4] else null),
    Leg4 = Table.AddColumn(Leg3, "Leg4", each if [Routing.4] = [Routing.5] then [Routing.5] &"-"&[Routing.6] else

if  [Routing.4] <> null and [Routing.5] <> null then [Routing.4]&"-"&[Routing.5] else null)
in
    Leg4