Forum Discussion
How to split a line in multiple lines based on all the integer values between two columns' values
I have lines with zipcodeFrom and zipcodeTo
For instance
ARMENIA ARTIK 3001 3007
I would like to get 7 lines, one for each possible zipcode : 3001,3002,3003, etc...
how can I do that using PowerQuery Editor ? I was thinking about adding a costum column looping between two values, with a comma separator : 3001,3002,3003,3004,3005,3006,3007
Then I think I could probably explode the line with the comma separator. But I don't know how to create this list of value. Is that possible with Power Query Editor ? Any help will be much appreciated ! 🙂
Hi, you can use "range" in M language.
In your table you have to add a Custom column like this
after that you will obtain a custom column and "Expand to new rows" on that column
will produce the belowe result
Now you can remove the "from" and "to" existing columns
Hope will help
3 Replies
Can you share sample data and sample output in table format?
Hi, you can use "range" in M language.
In your table you have to add a Custom column like this
after that you will obtain a custom column and "Expand to new rows" on that column
will produce the belowe result
Now you can remove the "from" and "to" existing columns
Hope will help
Oh very cool, I didn't know this syntax. And the "expand" option, I didn't know either, thanks a lot !
In the mean time, I found another solution to get the range : "List.Numbers(Number.FromText([Postcode]),[RangeSize]+1)" (with the second value being the size of the range, I created a custom column to calculate the difference between the two codes). But your solution is even simpler.
