cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lerecie
Frequent Visitor

Expand Dates in Large Data Set - PowerQuery

Reposting in the PowerQuery side o

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:

lerecie_0-1617144927666.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):

 

ResourceAllocationIdAllocationPercentageEndDateStartDate
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:

ResourceAllocationIdAllocationPercentageEndDateStartDateDate
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:

lerecie_1-1617144927654.png

 

(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:

 

Let

Source=PowerBI.Dataflows(null),

...

#"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}})
in
#"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).

 

Thanks!

1 ACCEPTED SOLUTION
lbendlin
Super User III
Super User III

You might be slightly overomplicating things. Remember that in DAX everything is a table.  

 

- prepare a reference table that lists all dates you consider to be the "beginning of the week" - whatever that means for you

- Generate a series of rows from start date to end date for each of your Resource Allocation IDs.

- Finally use the reference table as a filter on your generated series.

View solution in original post

1 REPLY 1
lbendlin
Super User III
Super User III

You might be slightly overomplicating things. Remember that in DAX everything is a table.  

 

- prepare a reference table that lists all dates you consider to be the "beginning of the week" - whatever that means for you

- Generate a series of rows from start date to end date for each of your Resource Allocation IDs.

- Finally use the reference table as a filter on your generated series.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors