Need help cleaning up a column and grouping it.

[Moses_Lau]

Hello all,

 The current data set I am working on has a column with such values.

1, 1 - 2, 4, 5 days, 9 days, 9-10 days etc etc.

It keeps on going until 50 ish days.

Is there a way to clean it up and group it down to a range of 5 days, eg 1-5, 6-10, 11-15, 16-20 and so on.

Your help is greatly appreciated!

[v-cgao-msft · Accepted solution]

Hi @Moses_Lau ,

Are there records distributed on either side of a multiple of 5? For example 13-17, 21-31. If so, how are they being considered for distribution?
Does the actual data look like this and not contain 'days' or other text?

Please try adding a custom column like:

let
a = Number.IntegerDivide(Number.From(Text.Start([Column1],1)),5)*5+1,
b = Text.From(a)&"-"&Text.From(a+4)
in 
b

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Syndicate_Admin]

Yes, Column By Example can handle this (although, you will need to provide a LOT of examples to make it work)