Solution to Excel BI challenge PQ133

excelbi-pq133.m

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // Prep the data for calculations later
    
    // In order to merge date and time, convert them to the appropriate types
    Retyped = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}}),
    
    // Merge the date and time into a single column
    DateTimed = Table.AddColumn(Retyped, "DateTime", each [Date] & [Time], type datetime),

    // Add an index for easier lookups
    Indexed = Table.AddIndexColumn(DateTimed,"Index"),

    // If the date on the previous index is different to the current row, calculate the time as a proportion of the
    // difference between the current datetime and the previous datetime
    AddFactor = Table.AddColumn(Indexed, "Factor",
        (curr) => let prev = try Indexed{curr[Index]-1} otherwise curr
                  in if curr[Date] <> prev[Date] 
                     then Number.From(curr[Time]) / Number.From((curr[DateTime] - prev[DateTime])) 
                     else 1),

    // Get the partial rows to duplicate and place in the previous date
    PartialRows = Table.SelectRows(AddFactor, each [Factor] <> 1),
    
    // Adjust the partial rows to prior date and factor = 1 - current factor
    NewRows = Table.TransformColumns(PartialRows, {{"Date", each Date.AddDays(_,-1)}, {"Factor", each 1 - _}}),

    // Add the new rows to the main table
    AddNewRows = Table.Combine({AddFactor, NewRows}),

    // Unpivot the meter columns
    Unpivot = Table.UnpivotOtherColumns(AddNewRows, {"Date", "Time", "DateTime", "Index", "Factor"}, "Meter", "Reading"),

    // Group by Meter, add calculation of consumption
    Group = Table.Group(Unpivot, {"Meter"},
        {{"Rows", (g) => Table.AddColumn(g, "Consumption", (curr) => try (curr[Reading] - g[Reading]{curr[Index]-1}) * curr[Factor] otherwise 0)}}
    ),

    // Expand Date and consumption
    Expand = Table.ExpandTableColumn(Group, "Rows", {"Date", "Consumption"}, {"Date", "Consumption"}),

    // Function to group by an arbitrary field (or no field) and sum the consumption for the groups
    fnSumValueBy = (optional groupBy) => Table.Group(Expand, if groupBy=null then {} else {groupBy}, {{"Consumption", each List.Sum([Consumption])}}),
    
    // Calculate the total consumption for each date
    RowTotals = fnSumValueBy("Date"), 
    
    // Calculate the total consumption for each Meter
    ColTotals = fnSumValueBy("Meter"), 
    
    // Calculate the total consumption for all dates and all meters
    GrandTotal = fnSumValueBy(), 
    
    // Add the total rows to the main table
    CombineWithTotals = Table.Combine({Expand, RowTotals, ColTotals, GrandTotal}), // Add the total rows to the table
    
    // Round the consumption to zero decimals in preparation for pivoting
    RoundConsumption = Table.TransformColumns(CombineWithTotals, {{"Consumption", each Number.Round(_,0)}}),

    // Fill blank columns on total rows
    // e.g. fnIfNull("Grand Total") becomes this function:
    // (arg) => if arg=null then "Grand Total" else arg
    // Which can then be used in Table.TransformColumns as follows:
    fnIfNull = (ifnull) => (arg) => if arg=null then ifnull else arg, 
    FillCols = Table.TransformColumns(
        RoundConsumption,
        {{"Date", fnIfNull("Grand Total")},
         {"Meter", fnIfNull("Total Consumption")}}),

    // Pivot to output shape
    Result = Table.Pivot(FillCols, List.Distinct(FillCols[Meter]), "Meter", "Consumption", List.Sum)
in
    Result