Power Query in Power BI - Column Parsing

Resolved · Medium Priority · Version 365

Tue 4 Aug 2020, 13:15 Delegate 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:07 Trainer 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:16 Delegate 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:59 Trainer 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.

Training courses

	98.7 % recommend us
	More live stats...

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Power BI tip:

Stay Updated and Engage with the Community

Power 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.

View all Power BI hints and tips