Lists can be a powerful thing, surely you have had a start and an end date at one point.
Sometimes we need all the dates between those dates, this is when lists come in handy.
A list from 1 to 10 in Power Query looks like this {1..10}
If you have start and end dates as well as an amount for this given period and you want to split it up, you can do this using the list operator {}
In step 1 we create a custom column with the following code
{Number.From([Start])..Number.From([End])}The first part is converting our Start column (which is a date) to a number (44197 would be 01.01.2021 e.g.) then we need those two dots (..) and then we have the End column (which again is a date) and that is also converted to a number. The whole thing is wrapped into some nice {} brackets which cover lists.
For every row of data we will get a list of all numbers between Start and End.
Next, we can "expand to new rows" which will expand our lists and give us all days (still as a number). Now, we can easily convert this column back into a date.
As the last step we will divide our yearly amount by 365 and so generate our daily amount. The data will now match this amount perfectly since we generated one row for every day.
There are endless use cases for this small but very powerful way of handling data in Power Query. Sometimes you might only need the dates between two dates, other times however you might be looking to allocate an amount evenly over time and this technique might just be perfect for that.
As always there are downsides since you might be generating millions of rows, especially if you have a lot of columns you should think about it beforehand. An alternative would be to do your calculations in DAX. Sometimes however Power Query might be more suitable to solve your problems.
Comments