Pivot rows depending on month interval using PowerQuery

Question

I have the following data set :

I want to pivot my data like below :

So taking the row below as an example:

The end result for this row would be as follows :

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

Answer 1

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