Forum Discussion
Appending different columns
Hi, v-juanli-msft
Thank you for your reply!
Even if the sensors don't send values at the same time, Is there a way to Visualize all the sensor values at the same time? So that I can see how much Airflow the ventilation sensor sends when one or more than one person is at the room?
Maybe by taking the average time or by using time intervals for example, in a 10 minutes interval?
I don't know how to do it, but the idea is to visualize the Airflow and body values at the same time.
Hi Anonymous
I can work out with the requirement: "using time intervals for example, in a 10 minutes interval"
you could slicer the time in a time interval as below
To get result above, go to Queries Editor, add custom columns in the "Append1" table
Add custome columns step by step
Date =DateTime.Date([time]) Time.1 =DateTime.Time([time]) Hour= Time.Hour([Time.1]) Minute= Time.Minute([Time.1]) Length= Text.Length(Text.From([Minute], "en-US")) Transform->Extract-> the first character->count(1), then Transform->change column “the first character “ type to “number” Custom =if [Length] > 1 then [First Characters] else 0 Custom.1= Text.Combine({Text.From([Hour], "en-US"),Text.Combine({Text.From([Custom], "en-US"),"0"},""),"00"}, ":")
Create a table like this in excel and import into power bi
| time interval | refernce |
| 9:30:00 | 9:30:00 AM ~ 9:40:00 AM |
| 9:40:00 | 9:40:00 AM ~ 9:50:00 AM |
| 9:50:00 | 9:50:00 AM ~ 10:00:00 AM |
| 10:00:00 | 10:00:00 AM ~ 10:10:00 AM |
| 10:10:00 | 10:10:00 AM ~ 10:20:00 AM |
| 10:20:00 | 10:20:00 AM ~ 10:30:00 AM |
| 10:30:00 | 10:30:00 AM ~ 10:40:00 AM |
| 10:40:00 | 10:40:00 AM ~ 10:50:00 AM |
| 10:50:00 | 10:50:00 AM ~ 11:00:00 AM |
| 11:00:00 | 11:00:00 AM ~ 11:10:00 AM |
| 11:10:00 | 11:10:00 AM ~ 11:20:00 AM |
| 11:20:00 | 11:20:00 AM ~ 11:30:00 AM |
Then go to Queries Editor, change the data type of "time interval" to Text.
Create a relationship betwwen this table and "Append1" table
Finally, add "time interval" in the slicer
Best Regards
Maggie