I have a PQ table that contains two columns, "From" and "To," which have numerical values from 0 to 10 000 000. I would like the user to define intervals in an Excel sheet that should be used to group numbers in another table.
The input intervals could look like:
From: To:
0 100 000
100 001 1 000 000
1 000 001 5 000 000
5 000 001 10 000 000
I have no problems grouping the records correctly (I do that by comparing the numbers to be grouped with if statements and the "To" numbers and I put the "From" and "To" numbers in two columns that should be easy to combine in one column with "-" as a separator. But would like to output the result to Excel with a heading showing the intervals as above but where the separator "-" is always centered irrespective of the intervals the user is choosing. I would also like to format the numbers with a thousand separator.
I have tried to convert the numbers to text using the Text.Padstart function, but somehow 0 with 9 leading zeros is not the same length as 100 001 with 3 leading zeros. Not a big issue really, I just feel my output report look unprofessional with the "-" zigsagging in the output column. I would really like them to look like this:
0-100 000
100 001-1 000 000
1 000 001-5 000 000
5 000 001-10 000 000
Thank you for your time and assistance.
The input intervals could look like:
From: To:
0 100 000
100 001 1 000 000
1 000 001 5 000 000
5 000 001 10 000 000
I have no problems grouping the records correctly (I do that by comparing the numbers to be grouped with if statements and the "To" numbers and I put the "From" and "To" numbers in two columns that should be easy to combine in one column with "-" as a separator. But would like to output the result to Excel with a heading showing the intervals as above but where the separator "-" is always centered irrespective of the intervals the user is choosing. I would also like to format the numbers with a thousand separator.
I have tried to convert the numbers to text using the Text.Padstart function, but somehow 0 with 9 leading zeros is not the same length as 100 001 with 3 leading zeros. Not a big issue really, I just feel my output report look unprofessional with the "-" zigsagging in the output column. I would really like them to look like this:
0-100 000
100 001-1 000 000
1 000 001-5 000 000
5 000 001-10 000 000
Thank you for your time and assistance.