Group By and Dataverse

[MagnusGöransson]

Hello,

I trying to solve something that is probably quite simple. I have an Arduino MKR 1010 (wifi) that counts the pulses from my energymeters. The Arduino posts to my table in Dataverse every five minutes, if there has been any electricity consumption during the last five minutes for a particular enegry meter. Below is a screenshop of the actual data

As you can see, then name column is the meter, Ventilation is the energy consumed by my FTX and "Elpatron" is my electrical heater (it makes hot water if i dont fire up the boiler).

What i would like to do is to group the data by hour and by meter (name column) so i can have a chart in a powerapp the show hour by hour consumption for each consumer ("Ventilation" and "Elpatron"). I will also add more meters when my EV arrives and i will also measure my garage.

This is what i have done so far:

ClearCollect(Col_EnergyReadings,AddColumns(EnergyReadings,"DateAndHour",Text('Created On',"[$-en-US]yyyy-mm-dd:hh")));

This works as expected and adds a column that i can use to group by hour. Then I'm lost. I have tried to groupby the new column:

ClearCollect(Col_EnergyReadingsGroupedByHour,GroupBy(Col_EnergyReadings,"crdd4_name", "DateAndHour"));

But then I cant reach the meter (name) column anymore. I would like to sum the result for each meter and for each hour into separate columns. Any ideas? Here is a photo of my hardware as a bonus 🙂

[RandyHayes]

@MagnusGöransson 

Oh wow...I love the mix of hardware and software like that!  I'm really glad you provided a picture of the gear!

Please consider changing your Formula to the following:

ClearCollect(Col_EnergyReadingsGroupedByHour,
    GroupBy(
        AddColumns(EnergyReadings,
            "DateAndHour", Text('Created On',"[$-en-US]yyyy-mm-dd:hh")
        ),
        "DateAndHour",
        "records"
    )
)

I hope this is helpful for you. 

[MagnusGöransson]

Thanks!

I can provide the Arduino code if any one needs it. Just PM me. The code is quite ugly, but its working and it uses the Dataverse Web API to upload, so no Flow is used. Just an Azure App registration and a DataVerse Application User to provide access.

I tried you formula and it works. However, I don't understand the last column "records". Where do that come from and what is it used for? I have tried this, but I dont think I understand how the data is structured now.

ClearCollect(Col_EnergyReadingsGroupedByHourSum,AddColumns(Col_EnergyReadingsGroupedByHour,"Sum",Sum(records.Name,records.Kwhvalue)))

[RandyHayes]

@MagnusGöransson 

That is so awesome.  I've got a few pi's sitting around and never find the time to do anything with them...one day I hope!

You can do all the things you need in one collection (actually, you don't even need a collection), but like this:

ClearCollect(Col_EnergyReadingsGroupedByHour,
    AddColumns(
        GroupBy(
            AddColumns(EnergyReadings,
                "DateAndHour", Text('Created On',"[$-en-US]yyyy-mm-dd:hh")
            ),
            "DateAndHour",
            "records" 
        ),
        "recordSum", Sum(records.Name, records.Kwhvalue)
    )
)

So when you GroupBy, you end up with two columns (or at least based on the formula I gave you).  One column is the main column you are grouping by.  In your case, the added column "DateAndHour" - this is like a Distinct on your data.  Then, you have an ability to name the next column that will be in your table - in this case we called it "records" and it is just a column that contains a table of all of the records that match that DateAndHour...which is why adding the "recordSum" column outside of that (and I called it recordSum just because it's like fingernails on a chalkboard seeing the name of a function or reserved name in a formula) and doing a Sum on the records will give us the sum we want.

Glad it is working out.

[MagnusGöransson]

Thanks a ton for your reply!

The Pi's are nice and have plenty of power. To much to learn though for me as a low code developer 🙂

I tried your formula, and it is basically what I already tried. I get this error:

The Sum function asks for a number which is odd. I mean if it wasn't a table i would just sum the numbers using +?

---

[RandyHayes]

@MagnusGöransson 

Actually I was basing the formula off of what you had before.  I think you are just trying to sum the Kwhours, so:

ClearCollect(Col_EnergyReadingsGroupedByHour,
    AddColumns(
        GroupBy(
            AddColumns(EnergyReadings,
                "DateAndHour", Text('Created On',"[$-en-US]yyyy-mm-dd:hh")
            ),
            "DateAndHour",
            "records" 
        ),
        "recordSum", Sum(records.Kwhvalue)
    )
)

If you have other values to sum or count or do other functions on in the records, you can just add those in as well.

 Like this:

ClearCollect(Col_EnergyReadingsGroupedByHour,
    AddColumns(
        GroupBy(
            AddColumns(EnergyReadings,
                "DateAndHour", Text('Created On',"[$-en-US]yyyy-mm-dd:hh")
            ),
            "DateAndHour",
            "records" 
        ),
        "recordSum", Sum(records, Kwhvalue),
        "recordCount", CountRows(records),
        "OtherSum", Sum(records, Other)
    )
)​

Yeah, I had great ideas for the Pi's but then distracted by other things...so, they have some dust on them now.  I hope to pick them back up soon.  I'm sure I'll then run into the "oh, you can't do that on that old model...you need version x"

[MagnusGöransson]

Went to bed 🙂

However, I have made some progress!

ClearCollect(Col_EnergyReadingsGroupedByHour,
    AddColumns(
        GroupBy(
            AddColumns(EnergyReadings,
                "DateAndHour", Text('Created On',"[$-en-US]yyyy-mm-dd:hh") & " " & Name
            ),
            "DateAndHour",
            "records" 
        ),
        "recordSum", Sum(records,Kwhvalue),
        "Enhet",First(records.Name).Name
    )
)

By adding "Name" to the column i use for grouping I get the data grouped by hour and device so i get a sum for each hour and device. Seems to do the trick!

Is it possible to add a column based on a distcint of "name"-column? I get this inception feeling when I dig into this 🙂

What i would like to achive is a table that looks like this:

Hour	Elpatron	Ventilation	Lägenhet	Garage	Garage heater
2021-02-24:01	0,02	0,001	0,3	0,2	0,2
2021-02-24:02	0,01	0,001	0,5	0,2	0,3

Is that possible?

[cholopa]

Hi:

It would be fantastic if you could share that code with me.

Thanks in advance..