Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |