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,
May I know is there any way I could split text before multiple Delimiters in one function?
Original Column | Desired Result |
1 & 5 | 1 |
50 - 1000 | 50 |
1000/2000 | 1000 |
2000&5000 | 2000 |
5000/10000 | 5000 |
Great thanks!
Solved! Go to Solution.
Add a step to extract the text before one of those delimiters, and then modify the code in the formula bar to look like this
= Table.TransformColumns(Source, {{"Column1", each Text.Start(_, Text.PositionOfAny(_, {"&", "/", "-"})), type text}})
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Add a step to extract the text before one of those delimiters, and then modify the code in the formula bar to look like this
= Table.TransformColumns(Source, {{"Column1", each Text.Start(_, Text.PositionOfAny(_, {"&", "/", "-"})), type text}})
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
If people (like me) don't get it working somehow, what are we doing wrong?
First creating a basic query step from the ribbon which works fine:
= Table.AddColumn(#"NameOfPreviousQueryStep", "Text Before Delimiter", each Text.BeforeDelimiter(Text.From([OriginalColumn]), "1"), type text)
But then changing this line into TransformColumns seems to miss a reference to OriginalColumn:
= Table.TransformColumns(#"NameOfPreviousQueryStep", {{"New column", each Text.Start(_, Text.PositionOfAny(_, {"&", "/", "-"})), type text}})
Gives errors with multiple attempts.
What now?
Two follow-on questions:
Thank you,
-Travis
how can this be done with a multiple character string?
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.