Forum Discussion

MFR's avatar
MFR
New Member
3 years ago

Splitter.SplitTextByAnyDelimiter when some delimiters and their values are missing

Hi !

 

I need to split values into several columns, according to several delimiters, and have the resulting values go into the column corresponding to their delimiter 😵. The delimiters always appear in the same order {"$$b","$$e","$$a","$$j"}.

Problem: in some cases, the source value does not contain all the delimiters and therefore not all the values to fill the columns. This is not a problem in itself, except that the next value would have to be shifted into its "correct" column.

 

See the following 3 tables for a better understanding.

 

Source

 

 

 

 

 

Expected

 

 

 

 

 

The best I can do is the following table, using :

= Table.SplitColumn(Base, "Local Param", Splitter.SplitTextByAnyDelimiter({"$$b ","$$e ","$$a ","$$j "}), {"$5","$b","$e","$a","$j"},"")

In orange the values that are not in their "correct" column. They should be in a cell further to the right.

 

The best I can do

 

 

 

 

 

Any idea ?

Thanks for your help !

Need Help

3 Replies

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

    Hi MFR 

     

    Download sample PBIX file

     

    You can add several Custom Columns and use Text.BetweenDelimiters 

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlExVTA2NTCzNDUxMFRQUUkC8oBUooKLq66RgaGRrgGIm6WQrhSrg1N1qoKbS7CCl66vQgBYr1eAv6efXoiBqaUJTHMsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Local Param" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Local Param", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "$5", each if Text.Contains([Local Param], "$$5") then Text.BetweenDelimiters( [Local Param] , "$$5 ", " $$") else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "$e", each if Text.Contains([Local Param], "$$e") then Text.BetweenDelimiters( [Local Param] , "$$e ", " $$") else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "$aCustom", each if Text.Contains([Local Param], "$$a") then Text.BetweenDelimiters( [Local Param] , "$$a ", " $$") else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "$j", each if Text.Contains([Local Param], "$$j") then Text.BetweenDelimiters( [Local Param] , "$$j ", " $$") else null)
in
    #"Added Custom3"

     

     

     

    Regards

     

    Phil

    • MFR's avatar
      MFR
      New Member
      3 years ago

      Hi Phil,

       

      Thank you very much for your proposed solution. I managed to get what I wanted !
      My final query in Excel is as follows :

       

      let
      Source = Excel.CurrentWorkbook(){[Name="Tableau6"]}[Content],
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Local Param", type text}}),
      #"Split Local Param by row" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Local Param", Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Local Param"),
      #"Custom$5" = Table.AddColumn(#"Split Local Param by row", "$5", each if Text.Contains([Local Param], "$$5") then Text.BetweenDelimiters( [Local Param] , "$$5 ", " $$") else ""),
      #"Custom$b" = Table.AddColumn(#"Custom$5", "$b", each if Text.Contains([Local Param], "$$b") then Text.BetweenDelimiters( [Local Param] , "$$b ", " $$") else ""),
      #"Custom$e" = Table.AddColumn(#"Custom$b", "$e", each if Text.Contains([Local Param], "$$e") then Text.BetweenDelimiters( [Local Param] , "$$e ", " $$") else ""),
      #"Custom$a" = Table.AddColumn(#"Custom$e", "$a", each if Text.Contains([Local Param], "$$a") then Text.BetweenDelimiters( [Local Param] , "$$a ", " $$") else ""),
      #"Custom$j" = Table.AddColumn(#"Custom$a", "$j", each if Text.Contains([Local Param], "$$j") then Text.BetweenDelimiters( [Local Param] , "$$j ", " $$") else "")
      in
      #"Custom$j"

       

      Regards,

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

        Hi MFR 

         

        Glad to hear it.

         

        If my answer solved your problem please mark it as the solution so others can learn from this.

         

        Regards

         

        Phil