Power Pivot query

[pjmorris]

I'm learning how to use the power pivot - which is an extraordinary improvement on normal pivottables. But I don't know the full capabilities. My challenge is this:

I have data arranged as follows (the real data is more complex):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Activity[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Total Effort[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1 Jan 2018[/TD]
[TD]31 Dec 2018[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1 Mar 2018[/TD]
[TD]30 Sep 2018[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1 Aug 2018[/TD]
[TD]31 Dec 2018[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

The output I would like to achieve would be:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]J[/TD]
[TD]F[/TD]
[TD]M[/TD]
[TD]A[/TD]
[TD]M[/TD]
[TD]J[/TD]
[TD]J[/TD]
[TD]A[/TD]
[TD]S[/TD]
[TD]O[/TD]
[TD]N[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Effort[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]25[/TD]
[TD]25[/TD]
[TD]25[/TD]
[TD]25[/TD]
[TD]25[/TD]
[TD]45[/TD]
[TD]45[/TD]
[TD]25[/TD]
[TD]25[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

I think that PowerPivot could do something like this, but can't work out how. If someone let me know, authoratitivly, whether to persist with PP or whether I need to take a different approach I'd be grateful.

Many thanks

Peter

 

[VBA Geek]

Hello pjmorris

what you are saying is achievable but you need to make some changes to your data. First of all you will need to create a Calendar Table

Second, you may need to modify the table you have posted, instead of having a start date and end date, I would keep just one date column and then duplicate each row N number of times depending on the time frame from Start Date to End Date

As an example, if you wanted to perform an analysis by month, the first row would become

Book1
	A	B	C
1	Activty	Date	Effort
2	A	1-Jan-18	5
3	A	1-Feb-18	5
4	A	1-Mar-18	5
5	A	1-Apr-18	5
6	A	1-May-18	5
7	A	1-Jun-18	5
8	A	1-Jul-18	5
9	A	1-Aug-18	5
10	A	1-Sep-18	5
11	A	1-Oct-18	5
12	A	1-Nov-18	5
13	A	1-Dec-18	5
Sheet1

 

[pjmorris]

Hi

Many thanks for the reply. I'll follow up with the Calendar link. The challenge will be changing the source data as suggested - I can see why, but obviously need to automate that step as users will not do so and there are many to be changed. Any suggestions about how to do this would help.

Regards

Peter

 

[VBA Geek]

You could use power query to apply the transformation, it should be something like this:

let
    // source table, change it to a table coming from your database, or excel
    Source = Table.FromRows(
                    Json.Document(
                            Binary.Decompress(
                                   Binary.FromText(
                                        "i45WclTSUTLU9UrM0zW0ADKNDXVdUpMhbDMDpVidaCUnsArfxCKoCgPd4NQCCNvQBKLEGazEsTQd0xBDA6CSWAA=", 
                                        BinaryEncoding.Base64), 
                                    Compression.Deflate)), 
                            let _t = ((type text) meta [Serialized.Text = true]) in type table [Activity = _t, #"Start Date" = _t, #"End Date" = _t, #"Total Effort" = _t]),
    
    //  transformations
    ChangedType = Table.TransformColumnTypes(Source,{{"Activity", type text}, {"Start Date", type date}, {"End Date", type date}, {"Total Effort", Int64.Type}}),
    
    ModifyTotalEffort = Table.AddColumn( ChangedType, "TotEffort", each [Total Effort ] / Duration.Days( [End Date] - [Start Date] ) + 1 , type number ),
    
    AddDaysList = Table.AddColumn( ModifyTotalEffort, "Dates", each List.Dates([Start Date], Duration.Days( [End Date] - [Start Date] ) + 1, #duration(1, 0, 0, 0) ), type list ),
    
    ExpandedDates = Table.ExpandListColumn(AddDaysList, "Dates"),
    
    RemovedColumns = Table.RemoveColumns( ExpandedDates, {"Start Date", "End Date", "Total Effort"} )
in
    RemovedColumns

 

[pjmorris]

Hi, I've been on this forum for ages and this has to be the most useful help I've received. Thank you so much.

Really appreciated.

Regards

 

[pjmorris]

Hi,

Just thought I'd share my adaptation of the query which resulted in the month view I think I need:

let
    Source = Excel.CurrentWorkbook(){[Name="tblSource"]}[Content],


    ChangedType = Table.TransformColumnTypes(Source,{{"Activity", type text}, {"Start Date", type date}, {"End Date", type date}, {"Total Effort", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(ChangedType, "DurationMonths", each Date.Month([End Date])-Date.Month([Start Date])+1+12*(Date.Year([End Date])-Date.Year([Start Date]))),
    ModifyTotalEffort = Table.AddColumn(#"Added Custom", "TotEffort", each [Total Effort]/[DurationMonths]),
    
    AddDaysList = Table.AddColumn(ModifyTotalEffort, "Dates", each List.Dates([Start Date], Duration.Days( [End Date] - [Start Date] ) + 1, #duration(1, 0, 0, 0) ), type list),
    
    ExpandedDates = Table.ExpandListColumn(AddDaysList, "Dates"),
    UniqueMonthlyID = Table.AddColumn(ExpandedDates, "Custom", each (Date.Year([Dates])-2000)*100+Date.Month([Dates])),
    #"Removed Columns" = Table.RemoveColumns(UniqueMonthlyID,{"Start Date", "End Date", "Total Effort", "DurationMonths", "Dates"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Custom", "Activity"})
in
    #"Removed Duplicates"