need some insights on transforming my data

[dcyper]

Hello,

I could use some assistance, I just can't seem to figure it out...

Power BI - Transforming data... 

I have a kind of "event log".  At this moment we only have 4 different events.  (we only get the first occurence of the event and the data is provided by a third party in this way, so we can't change the source layout)

Kind of this:

Room	Sensor	Event	Time occurence	Duration	Trigger
Room 1	Sensor 1	Event 1	18h00	00h05	manually
Room 1	Sensor 2	Event 2	19h30	00h02	door
Room 1	Sensor 3	Event 1	03h00	00h50	manually
Room 1	Sensor 1	Event 3	09h00	00h15	manually

Now i would like to have 1 record per room/sensor with all the information of the 4 events in one row, like this

I could use a transform so i have 4 tables, one table per event...  These should then be "merged" as a full join, on room and sensor, to a new table...

But I want my solution to be dynamically.  So when we get event 5, 6, ... we don't have to change our preparation of the data and our client (who is building the report) just gets extra columns on the table with event 5, 6,... 

Thanks for al your insights

Kind regards

dcyper

[dcyper]

Thanx for your insights, i will look into the loop function.

I thought, by putting the events in the same row, it would be easier to calculate the "lead times (time between two events...)" and the avg, max, min, ... of those events.  (it's our client who will be building the report, so it would be clearer...)

[tackytechtom]

Hi @dcyper ,

I just took a couple of minutes to walk through this in my head. For now, I could only come up that you would need to write a function that loops over your events and adds these new columns. That is not trivial.

I am wondering what is wrong with the data that is in place? Anything specific you'd like to achieve? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/