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:

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!).

I take the default range provided in the ‘Create Table’ dialog and indicate that my data has headers.

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.

The delimiter I want to use is the dash (‘-‘).

This gives me two columns which I rename Start Date and End Date for clarity.

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.

I’m hoping this copes with all the date formats used by my salespeople.

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.

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.

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.

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.

I should now be able to change the data type to ‘Date’ on my new columns.

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.

I can now add a new custom column to create the list.

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.

I can see that the list contains the values; now I can move to step 4, which is to expand my column.

I choose to ‘Expand to New Rows’.

I have a row for each day. Now, I need to delete Start Date and End Date and convert Dates List to a date.

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!