Power Query: Arranging a List
28 April 2021
Welcome to our Power Query blog. This week, I look at how to translate a range of data contained in one cell to a list of cells.
Yet again, I have some data from my imaginary salespeople:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
I had asked for their diary of supplier contacts, but I have instead received a list of date ranges for each salesperson and supplier. I want to have a row for each date. I start by extracting my data to Power Query using ‘From Table / Range’ on the ‘Get & Transform Data’ section of the Data tab (as usual!).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I take the default range provided in the ‘Create Table’ dialog and indicate that my data has headers.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
Ultimately, I want to have a row for each date in the range. The steps I need to take in order to achieve this are:
- Create columns for the start and end date
- Ensure that the columns have data type date
- Create a list of all dates between the start and end date
- Ensure that this list is attached to the correct sales data.
In order to create the start and end date columns, I need to split Date Range by delimiter, which I can do from the Home tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
The delimiter I want to use is the dash (‘-‘).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
This gives me two columns which I rename Start Date and End Date for clarity.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
The next step is to set these columns to data type ‘Date’. I can do this in several places; I choose to select both columns and then right-click, where I can ‘Change Type’ to Date.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I’m hoping this copes with all the date formats used by my salespeople.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
Unfortunately, this is not the case. Only the dates using a forward slash (‘/’) have been correctly converted. As I did in Power Query: Dating Options, I need to convert the columns to the format that is not being correctly formatted. I need to remove the delimiters.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
I delete the ‘Changed Type2’ step and remove all the delimiters. Next, I have to create a custom column from the ‘Add Column’ tab where I put the delimiters into each date. Although the year length varies, I am only concerned with putting a forward slash after the second and fourth characters.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
The M code I have used is:
= Text.Combine({Text.Start([Start Date],2),"/",Text.Middle([Start Date],2,2),"/",Text.Middle([Start Date],4)})
This takes the first two characters, adds a forward slash, then adds the net two characters, adds another slash, and then adds the remaining text. Finally, the elements are combined into one text string.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I repeat the process for the end date. Note that if there is a space at the beginning of End Date, the positions will have to be adjusted accordingly. I can then delete my original date columns and rename my new columns Start Date and End Date.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image12.png/a1537847463e660a31158c8032525438.jpg)
I should now be able to change the data type to ‘Date’ on my new columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image13.png/917da985be13220165c8d2823e95344f.jpg)
Step 2 is complete, now I need to create a new custom column which will contain all the dates in the range. For this, I am going back to basic list creation, where I can use the ellipsis (..) to fill in the missing dates. For more on creating lists, see Power Query: Birthday Lists.
Having checked my dates are valid, I need to convert the columns to be whole numbers. This will allow me to create the list, as the ellipsis will not currently work with dates. It’s important to add a new ‘Change Type’ step for this, as I wouldn’t have been able to create a whole number from the text value with forward slashes in it.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
I can now add a new custom column to create the list.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
The M code I have used is:
= {[Start Date]..[End Date]}
This creates a list of numbers from Start Date to End Date, which I will be able to convert back to dates.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image16.png/d082e3477129350b8a2a589156028e63.jpg)
I can see that the list contains the values; now I can move to step 4, which is to expand my column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image17.png/a468c9c28195e8f18fdedfa59a0cf042.jpg)
I choose to ‘Expand to New Rows’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image18.png/61935f470d8b9f21b7b7683ffca40895.jpg)
I have a row for each day. Now, I need to delete Start Date and End Date and convert Dates List to a date.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/231/image19.png/06909e3fbfc8faf87de7555c0b99e9d3.jpg)
I can see that I have a row for each date with all the relevant data.
Come back next time for more ways to use Power Query!