How to split a line in multiple lines based on all the integer values between two columns' values

[François]

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

[pceglie · Accepted solution]

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

[François]

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. 

[Ahmedx]

Can you share sample data and sample output in table format?