Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
What is the best way to extract data such as this into another column:
If i have an original field called Col name and i wanted to normailse this as shown in the Normalised column, what formula would i need to write?
In basic format, the logic would be as follows:
Read the characters of the Col Name until you get to the first '-', Ignore all chars before the '-', if another '-' exists take all chars between this and the orginal '-' and store in the new column(Normailsed Name), otherwise just take the rest of the chars and store in the new column (Normailsed Name) - see expected output below:
Col Name | Normalised Name | |
abc - abc123 | abc123 | |
xyz - xyz123 - 456 | xyz123 |
Any ideas?
Solved! Go to Solution.
You can just add a new column with this formula
= Text.BetweenDelimiters([Col Name], "-", "-")
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @Mal_Sondh
Further to that, wrap it in Text.Trim to remove leading and trailing space characters
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Trim(Text.BetweenDelimiters([Col Name], "-", "-")))
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
Hi @Mal_Sondh
Further to that, wrap it in Text.Trim to remove leading and trailing space characters
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Trim(Text.BetweenDelimiters([Col Name], "-", "-")))
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
You can just add a new column with this formula
= Text.BetweenDelimiters([Col Name], "-", "-")
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
128 | |
108 | |
99 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |