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