Forum Discussion

ViviHad's avatar
ViviHad
Regular Visitor
3 years ago

Split text columm with date and # delimiter

Please, I have a string columm (obs) with date and text. I need to split the text and date in the Obs column (see BEFORE) into a separate row for each Funci (see AFTER), using the # as a delimiter...
Need Help
AKNiang's avatar
AKNiang
Frequent Visitor
3 years ago

Hi,

 

The best I reached is the folowing code:

 

 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Funci", type text}, {"obs", type text}}),
#"Filled Down" = Table.FillDown(#"Changed Type",{"Funci"}),
#"Lowercased Text" = Table.TransformColumns(#"Filled Down",{{"obs", Text.Lower, type text}}),
Custom1 = Table.AddColumn(#"Lowercased Text", "Date", each Text.Remove([obs],{"a".."z", "#"})

)
in
Custom1

 

To make it work you have to add after the "#" all the alphabetical and special characters that are not covered in my code.

 

 Hope have helped you a little bit:

 

You can follow me on LinkedIn -->Abdoul Karim Niang | LinkedIn

 

  • ViviHad's avatar
    ViviHad
    Regular Visitor
    3 years ago

    Hi Abdoul.

     

    Thanks for the answers, but my table is not like that.

     

    The column "Funci" has a value (like F123456) and the column "obs" has several lines in the same cell.

     

    Here is a photo of my table:

     

    In the first line for example, the output must be something like:

     

    Funci               Date              Job

    F1713097        03/11/22      #assessoria

    F1713097        27/10/22      #envio HISCRE #assessoria

     

    and it goes in all lines.

     

    Thanks for the effort to trying to resolve my problem 🙂

     

    • AntrikshSharma's avatar
      AntrikshSharma
      Community Champion
      3 years ago

      ViviHad To make sure the data formats are retained correctly, you should upload the Excel file on Google Drive and share a link otherwise people here will end up with solutions and you will not accept because of minute changes.