Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Expand Dates in Large Data Set - PowerQuery and Dax

I have a large data set in a proper star schema. However, i'm struggling with some reports out of it.

I'll explain what i'm currently doing, and hopefully someone can tell me a better way (or more elegant) way to accomplish something similar.


Data set:

Screenshot 2021-03-29 114022.png


There's many more tables that have proper relationships (Aprox 30 more), but for the purpose of this exercise, that's all we need.

Most of the work happens in the ResourceActualDetail table it looks like this (with some columns deleted):


79414/1/2018 0:002/25/2018 0:00
7951.153/4/2018 0:002/25/2018 0:00
7951.08753/11/2018 0:003/4/2018 0:00
79514/1/2018 0:003/11/2018 0:00
7961.053/4/2018 0:002/25/2018 0:00
7970.7253/4/2018 0:002/25/2018 0:00
153113/11/2018 0:002/25/2018 0:00
172114/1/2018 0:002/25/2018 0:00



Desired Output:

79414/1/2018 0:002/25/2018 0:002/26/2018 0:00
79414/1/2018 0:002/25/2018 0:003/4/2018 0:00
79414/1/2018 0:002/25/2018 0:00
79414/1/2018 0:002/25/2018 0:004/1/2018 0:00

Notice the 7 day increments.


And the same for every ResourceAllocationID so that I can graph it like so:


(error in Graph dates, should be with 7 day increments).


Where there is a different line for each Resource (connected through relationships to ResourceAllocation and ResourceActualDetail). Even if we can get it to a point where we can do that for each ResourceAllocationId I can figure out the rest 🙂


Current Approach:

These data tables are quite large already(~1M rows) , and my approach only has bloated that.

Trying to do everything as close to the source, this what I came up with:





#"Changed Type" = Table.TransformColumnTypes(ResourceActualDetail1,{{"EndDate", type number}, {"StartDate", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each List.Numbers([StartDate],([EndDate]-[StartDate])/7,7)),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type datetime}, {"EndDate", type datetime}, {"StartDate", type datetime}})
#"Changed Type1"


Essentially, I change the StartDate and EndDate fields to numeric Values and then I calculate the beginning of each week before i switch back all those values to Date type.

This part of the query doesn't fold, so I cannot take the advantage of the PowerFlow to do it. It also makes each refresh very slow (should be refresed multiple times a day).


Any suggestions or ideas?? Obviously looking for the most efficient way to achieve this so that it won't take many resources (I do have a premium instance).




Frequent Visitor

I have tried both approaches and there's something that is not quite working.


I have focused in using your Value by End of the Month formula, since it looks the most complete for what I am trying to do, but it doesn't work.. i don't get the continuos dates and get breaks in the data still.


Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors