Pivot rows depending on month interval using PowerQuery

Sandra Guilep 21 Reputation points
2021-01-22T22:39:24.697+00:00

I have the following data set :
59650-image.png

I want to pivot my data like below :

So taking the row below as an example:
59706-image.png
The end result for this row would be as follows :
59678-image.png

The cost will be divided by the duaration in days between the satrt and the end day.
Same for the consumption and emission columns.

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
36,564 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Lz._ 8,991 Reputation points
    2021-01-23T05:36:07.69+00:00

    Hi @AmiraBedhiafi-1845

    Assuming data set in Table1 (edited):

    let
        Source = Table1,
        Measures = {"Cost","Consumption","Emission"},
        Days = Table.AddColumn(Source, "Days", each
            Duration.Days([End Date]-[Start Date])+1, Int64.Type
        ),
        Dates = Table.AddColumn(Days, "Date", each
            List.Dates(
                [Start Date],
                [Days],
                #duration(1,0,0,0)
            ), type list
        ),
        RemovedColumns = Table.SelectColumns(Dates,
            {"Date","Days"} & Measures
        ),
        Daily = List.Accumulate(Measures, RemovedColumns,
            (state,current) =>
                Table.RemoveColumns(
                    Table.AddColumn(state, "_"&current&"_", each
                        Record.Field(_,current)/[Days], type number
                    ),
                    {current}
                )
        ),
        RemovedDays = Table.RemoveColumns(Daily,{"Days"}),
        NewMeasures = List.Transform(Measures, each "_"&_&"_"),
        RenamedColumns = Table.RenameColumns(RemovedDays,
            List.Zip({NewMeasures, Measures})
        ),
        ExpandedDate = Table.ExpandListColumn(RenamedColumns, "Date"),
        TypeDate = Table.TransformColumnTypes(ExpandedDate,
            {{"Date", type date}}
        )
    in
        TypeDate
    

    Corresponding XL/PQ sample avail. here

    1 person found this answer helpful.