Conversion of Cumulative value to Weekwise value of Primavera P6 Dump

Power Query
1

I have Primavera P6 dump. In this dump, manhours distribution is at cumulative level.

I need to convert at week level.

e.g.
AA- 20 40 50 60 70 70… ( P6 Dump values)
AA- 20 20 10 10 10 0 … ( Output expected)

I have attached sample input & output table in attached excel file.

Thanks in advance
Activity Distribution.xlsx (12.7 KB)

2

Hi @prafullchavan1,

The current format of your data is not optimized. However if you are receiving the data in this way and you also want to transform it, whilst set up like this, you obviously can.

I’ve created a function called: fxUpdateRecord, that generates a replacement record. The AddCustom-step illustrated how this custom function is invoked.

let
    fxUpdateRecord = ( excludeCols as list, inputRecord as record ) as record =>
    let
        allFieldNames = Record.FieldNames( inputRecord ),
        omittedFields = Record.SelectFields( inputRecord, excludeCols, MissingField.Ignore ),
        keptFields = Record.SelectFields( inputRecord, List.Difference( allFieldNames, excludeCols ), MissingField.Ignore ),
        keptFieldValues = Record.ToList( keptFields ),
        transformFields = List.Transform( {0..List.Count(keptFieldValues)-1}, 
            each keptFieldValues{_} - (try keptFieldValues{_-1} otherwise 0)
        ),
        updatedRecord = omittedFields & Record.FromList( transformFields, Record.FieldNames(keptFields))
    in
        updatedRecord,
    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    AddCustom = Table.AddColumn(Source, "InvokedFunction", each fxUpdateRecord( {"Activity Id" }, _ )),
    DrillDown = AddCustom[InvokedFunction],
    TableFromRecords = Table.FromRecords( DrillDown )
in
    TableFromRecords

With this result.

image
image1792×102 8.87 KB

Here is your sample file.
Activity Distribution.xlsx (190.2 KB)

I hope this is helpful.