How to calculate Energy consumption (in kWh) of a machine on daily basis for a specific time.

[skumar0105]

Hi Team,

i want to calculate Energy consumption of a machine from a Totaliser column named as 'Machine X Active Energy (units)', this is a time series data and the Energy units are taken from Energy meters.

i need to calculate the consumption in kWh from previous day 07:00 AM to current day 07:00 AM.

My data looks like this:

Date	Time	Machine 1 Active Energy (units)	Machine 2 Active Energy (units)	Machine 3 Active Energy (units)
26.07.2022	06:57:31	7269272674.668676	12845471589.393553	18123974201.95564
26.07.2022	00:22:31	7267540644.583843	12845379641.58878	18123786079.53212
26.07.2022	18:27:31	7266245906.03958	12845061335.796207	18123426670.89311
26.07.2022	11:17:31	7266245906.03958	12844777211.476612	18123273023.622402
26.07.2022	07:02:31	7265416899.93979	12844460165.42598	18123089148.049168
25.07.2022	07:07:31	7265357109.058804	12844289521.827116	18122760262.45503
25.07.2022	23:32:31	7265276500.578871	12843961707.86906	18122325606.47833
25.07.2022	12:17:31	7265145305.045862	12843756481.261644	18122081476.525288
25.07.2022	07:02:31	7265026727.554211	12843555771.599396	18121898035.09134

The calculated column will look like this:

Date	Machine 1 Energy in kWh	Machine 2 Energy in kWh	Machine 3 Energy in kWh
26.07.2022	1568.00	923.00	2340.00
25.07.2022	1289.00	845.00	2245.00

i am new to Power BI, kindly help with query.

Regards

Satish

[ronrsnfld]

Could you please show the actual numbers you are using from your data table to come up with the expected results for Machine 1.  If I use the values closest to 7AM, I get very different results from what you show.

[skumar0105]

Hi @ronrsnfld 

Please ignore the numbers mentioned in output table, this was shown for example only. actual results may vary.

please refer below screen shot:

[ronrsnfld]

Here is a solution based on your textual example data.

I tried to write it so you could add any number of machine columns. But if your real data is much different from what you presented as text data, you may need to adapt the code to your actual situation.

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

//Need this list so query can be dynamic with any number of machines
//  Seems like the ending "Active Energy (units)" is the key
    Machines = List.Select(Table.ColumnNames(Source), each Text.Contains(_,"Active Energy (units)")),
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Date", type date}, {"Time", type time}} & 
        List.Transform(Machines, each {_, type number}) ,"en-150"),

//Seconds to 7 AM (for calculating "closest")
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Seconds to 7AM", 
        each Number.Abs(Duration.TotalSeconds(([Date] & #time(7,0,0)) - ([Date] & [Time])))),
    
//Group by date and extract row with closest to 7AM time
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Date"}, {
        {"7 AM", (t)=> Table.SelectRows(t, each List.Min(t[Seconds to 7AM]) = [Seconds to 7AM])}
        }),
    #"Expanded 7 AM" = Table.ExpandTableColumn(#"Grouped Rows", "7 AM", {"Time"} & Machines),

//reset the data types
    reType = Table.TransformColumnTypes(#"Expanded 7 AM", {{"Time", type time}} & List.Transform(Machines, each {_, type number})),

//unpivot the "machine" columns
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(reType, {"Date", "Time"}, "Attribute", "Value"),

//Group by attribute (machine name)
//  Then calculated monthly change = kWh consumed for the month
    #"Grouped Rows1" = Table.Group(#"Unpivoted Other Columns", {"Attribute"}, {
        {"Monthly kwH", (t)=>
            Table.AddColumn(
                Table.FromColumns(
                    Table.ToColumns(t) & 
                    {{null} & List.RemoveLastN(t[Value],1)},
                    type table[Date=date, Time=time, Attribute=text, Value=number, shifted=number]), 
            "Month kwH", each ([shifted] - [Value]) / 1000, type number)}
        }),

//expand the grouped tables
    #"Expanded Monthly kwH" = Table.ExpandTableColumn(#"Grouped Rows1", "Monthly kwH", {"Date", "Time", "Month kwH"}),

//Change Machine name to become the new column headers after pivoting
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Monthly kwH","Active Energy (units)","Energy in kWh",Replacer.ReplaceText,{"Attribute"}),

//Pivot on the Attribute column with no aggregation
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute]), "Attribute", "Month kwH"),

// reset the data types, then by date descending
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",
        {{"Date", type date}, {"Time", type time}} & 
            List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Pivoted Column"),2), each {_, type number})),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Date", Order.Descending}})
in
    #"Sorted Rows"

Source Data

Results

[v-jingzhang]

Hi @skumar0105 

What is the current unit of values in 'Machine X Active Energy (units)' columns? Can you please take Machine 1 Energy as an example to help elaborate how to calculate using the current data to get the result 1568.00 and 1289.00 for the listed two dates? Currently I don't understand how to get these results. 

Best Regards,
Community Support Team _ Jing

[skumar0105]

Hi @v-jingzhang 

Currently the 'Machine X Active Energy (units)'  are recorded in kw only.

the data provided in table above is just for the explanation, the source is pushing the data at every 5 mins interval in a sql database. i.e. there are 12 rows per hour and 288 rows per day are recorded.

now i want to calculate each machine's daily Active Energy Units in kWh for a give period of 24 hours.

i.e. Active energy units (kw) = date.today(29.07.2022), time 07:00:00 - date.yesterday(28.07.2022), time 07:00:00.

kWh =  Active energy units (kw)/1000   ------------>(since 1 kWh = kwxno. of hours./1000)