Forum Discussion

LiliLPo's avatar
LiliLPo
Frequent Visitor
3 years ago
Solved

Spliting column by different delimiters and different location of the text

Hi, I realy need help to extract the data(comment part) above the yelow line and this needs to be dynamic. 

3 problems that I have: 

1. the comment is not always at the same place 

2. the delimitars for start and end are different. 

3. The length of the comment is different in each row

 

I have tried many solutions that I have found online but nothing realy works 

I will appriciate forany advice/help with this 

Thanks Lili

 

 

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    3 years ago

    You do not state how you want the output represented, so I am presenting it as concatenated text strings (concatenated with a Line Feed)

    • Split on the colon
    • Then split each sublist on the /
    • The comments will be the last item in each sublist
    • If that last item is blank ("") then delete it
    let
    Source = Table.FromColumns(
        {{"PLR/93170/TC heavy rates. premier Test:BON/17423/",
         "PLR/65559/retail falt rate, 40 ft RV Test Inc FSC 253.90",
         "BAS/68050/Test Verified, closest available driver:BON/00276/1st call Member gave wrong location"
        }}, type table[Column1=text]
    ),
    #"Added Custom" = Table.AddColumn(Source, "comment", each Text.Combine(
            List.RemoveItems(
                List.Accumulate(
                    List.Transform(Text.Split([Column1],":"),
                        each Text.Split(_,"/")), 
                    {},
                    (state, current)=>
                    state & {List.Last(current)}),
                {""}),
            "#(lf)"))
in
    #"Added Custom"

     

     

     

2 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    3 years ago

    You do not state how you want the output represented, so I am presenting it as concatenated text strings (concatenated with a Line Feed)

    • Split on the colon
    • Then split each sublist on the /
    • The comments will be the last item in each sublist
    • If that last item is blank ("") then delete it
    let
    Source = Table.FromColumns(
        {{"PLR/93170/TC heavy rates. premier Test:BON/17423/",
         "PLR/65559/retail falt rate, 40 ft RV Test Inc FSC 253.90",
         "BAS/68050/Test Verified, closest available driver:BON/00276/1st call Member gave wrong location"
        }}, type table[Column1=text]
    ),
    #"Added Custom" = Table.AddColumn(Source, "comment", each Text.Combine(
            List.RemoveItems(
                List.Accumulate(
                    List.Transform(Text.Split([Column1],":"),
                        each Text.Split(_,"/")), 
                    {},
                    (state, current)=>
                    state & {List.Last(current)}),
                {""}),
            "#(lf)"))
in
    #"Added Custom"