98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Power BI training and help » Power Query in Power BI - Column Parsing
Power Query in Power BI - Column Parsing
Resolved · Medium Priority · Version 365
Tue 4 Aug 2020, 13:15Delegate Alex said...
Alex has attended:
Excel Advanced course
Power Query in Power BI - Column Parsing
I want to parse the following column, which is a combination of numbers and text groups:
92.71
n/d(<25m)
2.71
n/d(25-50m)
12.77
n/d(50-100m)
n/d(100-250m)
n/d(250m+)
etc...
The result should look like this:
50-100m
<25m
<25m
25-50m
<25m
50-100m
100-250m
250m+
etc...
I am able to parse the groups by removing digits (i.e. n/d(250m+) --> 250m+), but I cannot figure out how to use logical formula as well to categorise the numbers into groups.
Thanks,
Alec
Tue 4 Aug 2020, 16:07Trainer Jens said...
RE: Power Query in Power BI - Column Parsing
Hi Alex,
Thank you for the forum question.
Add a calculated column in the table in Power Bi. Then just use the If function and test the numbers. If your column with the numbers you want to test is called let us say Values the formula should look like this in the calculated column:
Category=If([Values]>250000000,"250M+",If([Values]>=100000000,"100-250M",If([Values]>=50000000,"50-100M",etc......)))
Make sure you have the same number of close brackets as open.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 5 Aug 2020, 09:16Delegate Alex said...
RE: Power Query in Power BI - Column Parsing
Thanks Jens!
I was trying to complete the parse in Power Query, but now see it is far better to do it in a calulated column, where I can use IF functions.
Cheers,
Alex
Wed 5 Aug 2020, 10:59Trainer Jens said...
RE: Power Query in Power BI - Column Parsing
Good I am happy that I could help.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 12 Aug 2020: Automatically marked as resolved.
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Stay Updated and Engage with the CommunityPower BI is continually evolving, with new features and updates being released regularly. Stay informed about the latest enhancements by regularly checking the Power BI blog and community forums. Engaging with the Power BI community provides opportunities to learn from others, share your experiences, and get insights into best practices. |
