Split Column by Delimiter-by using different value condition

[Anonymous]

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:

Category	Input
UPI	UPI/02451574525388/15:09:43/UPI/YYYYYY
UPI	UPI/02451457297497/14:50:49/UPI/XXXXXX
NEFT	NEFT-282414563872-AAAAAAA
NEFT	NEFT-206721296-BBBBBB
Charges	Charges for PORD Customer Payment :460297250
Charges	Charges for PORD Customer Payment :460297244

Thanks,

[Fowmy · Accepted solution]

@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

[amitchandak]

@Anonymous , One way is to replace - and: by / and then use split

https://www.howtoexcel.org/power-query/bulk-replace-values/

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/