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.
I have a column like as below with a delimiter ";"
41.0223979,29.019951;41.0029523,29.0507535;41.0542336,28.9300755;40.999712,28.9225336 |
which i would like to convert like as below
41.0223979,29.019951wp0141.0029523,29.0507535wp0241.0542336,28.9300755wp0340.999712,28.9225336 |
Simply replacing each ";" into "wp??"
?? means increasing number for each value
Can you please support how to do that?
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcnBDcAgDAPAXXijKHFwqdVREPuvQcr3bq02whxITXXIPCTG96NDRF6kTyavciDz6XhN6cWlbpJm4CLA+rb3AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Orig = _t]),
#"Repl Delim" = Table.AddColumn(Source, "Repl Delim", each List.Accumulate({1..List.Count(Text.PositionOf([Orig], ";", 2))}, [Orig], (s,c) => Text.ReplaceRange(s, Text.PositionOf(s, ";"), 1, "pw" & Text.PadStart(Text.From(c), 2, "0"))))
in
#"Repl Delim"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Here is another way to do it, but the one by @CNENFRNL is more elegant. Add a custom column and put this formula in the pop-up box, replacing [StringColumn] with the name of your actual column.
let thislist = Text.Split([StringColumn], ";"),
wplist = List.Transform({1..List.Count(thislist)-1}, each "wp"& Text.PadStart(Number.ToText(_), 2, "0"))&{""},
zipped = List.Zip({thislist, wplist}),
concatenated = List.Transform(zipped, each Text.Combine(_, ""))
in
Text.Combine(concatenated, "")
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcnBDcAgDAPAXXijKHFwqdVREPuvQcr3bq02whxITXXIPCTG96NDRF6kTyavciDz6XhN6cWlbpJm4CLA+rb3AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Orig = _t]),
#"Repl Delim" = Table.AddColumn(Source, "Repl Delim", each List.Accumulate({1..List.Count(Text.PositionOf([Orig], ";", 2))}, [Orig], (s,c) => Text.ReplaceRange(s, Text.PositionOf(s, ";"), 1, "pw" & Text.PadStart(Text.From(c), 2, "0"))))
in
#"Repl Delim"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |