Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Split Column by Delimiter-by using different value condition

Hi All,

 

I have data like below table i want to "Split Column by Delimiter"  by using different value condition

Ex:

If Category = UPI then Split by "/" & first three value 

If Category = NEFT then Split by "-" & last value 

If Category = Charges then Split by ": " & last value 

 

 

Data:

CategoryInput
UPIUPI/02451574525388/15:09:43/UPI/YYYYYY
UPIUPI/02451457297497/14:50:49/UPI/XXXXXX
NEFTNEFT-282414563872-AAAAAAA
NEFTNEFT-206721296-BBBBBB
ChargesCharges for PORD Customer Payment :460297250
ChargesCharges for PORD Customer Payment :460297244

 

Thanks,

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    Paste the below code in the Advanced Editor of a Blank Query and check the step:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nY49D4IwEIb/CuksaTnuWtpNURMWJUYTDWFgqLgAho+Bf2+pbjr5DPeRe5/kioKl1Wjrrp/ZimXtcxpZuSrYJc/c7ioXgBSRQgKKk4RHZIQ2GPPldvP8EpAUaIVa8QgNCYPaC1ePFw67/dkZSwshAXSKjBMF4frNd0hIBRFoGW48PpA+qr62g8t8puDe9UF+PG2DdBrGrrFuq+bGtmNgUAr3FJD4W0VkZfkC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    AddColumn = Table.AddColumn(#"Promoted Headers", "Extracted", each 
    if [Category] = "UPI" then
    Text.BeforeDelimiter([Input], "/", {1, RelativePosition.FromEnd}) else if [Category] = "NEFT" then
    Text.AfterDelimiter([Input], "-", 1) else if [Category] = "Charges" then
    Text.AfterDelimiter([Input], ":") else null)
in
   AddColumn

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn

     




2 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

    Paste the below code in the Advanced Editor of a Blank Query and check the step:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nY49D4IwEIb/CuksaTnuWtpNURMWJUYTDWFgqLgAho+Bf2+pbjr5DPeRe5/kioKl1Wjrrp/ZimXtcxpZuSrYJc/c7ioXgBSRQgKKk4RHZIQ2GPPldvP8EpAUaIVa8QgNCYPaC1ePFw67/dkZSwshAXSKjBMF4frNd0hIBRFoGW48PpA+qr62g8t8puDe9UF+PG2DdBrGrrFuq+bGtmNgUAr3FJD4W0VkZfkC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    AddColumn = Table.AddColumn(#"Promoted Headers", "Extracted", each 
    if [Category] = "UPI" then
    Text.BeforeDelimiter([Input], "/", {1, RelativePosition.FromEnd}) else if [Category] = "NEFT" then
    Text.AfterDelimiter([Input], "-", 1) else if [Category] = "Charges" then
    Text.AfterDelimiter([Input], ":") else null)
in
   AddColumn

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn