Forum Discussion
Appending different columns
Hello, I have three queries with body sensor values, which contains columns Time, Pir(body movement) and body(body appearance) which are measured every 10 mins, and all of the queries have same columns, but they are not measured at the same time.
I also have a ventilation query which contains the time and airflow columns, the airflow is measured every 10 minutes.
I want to visualize how the ventilation works(how much airflow it sends) when there is someone in the room(body value from the 4 body sensors or when it senses a body movement around(Pir), and how much airflow the ventilation sends based on those values.
So I want to Join ( append) the three body sensor queries with the Ventilation query.
What I want to visualize is time, body (with a legend to distinguish the body values of the 3 body sensors), airflow and Pir
1, Can I append the three body queries with the ventilation query? How can I append them when they have different columns?
2. How can I display Time, Airflow, Pir and body(with a legend to show the four body values)?
below is the sample of my data
3. How can I use a legend to show the different body values when I have 3 values on the Values section(which visualization should I use)?
Thanks in advance!
8 Replies
I'd do it in this format:
Time
Sensor
Value
Body sensor 1
1
Body sensor 2
1
Body sensor 3
0
Ventilation sensor
16
Hi Anonymous
Could you give an example output table after appending these table?
I don't understand which row in table "ventilation " should connect to each row of the body sensor1, body sensor2, body sensor3 tables.
Best Regards
Maggie
Hi, v-juanli-msftOne of my questions is how I can append the body Sensor data with the Ventilation date. I want to use time and airflow from the ventilation table, to visualize how much Airflow it sends when someone is in the room and how many persons(based on the body sensors we can know how many people are there when the body value shows 1)
Is there a way to append those tables when they don't have similar columns?
Hi Anonymous
In "Body sensor1", "Body sensor2", "Body sensor3"
create custom columns named "Sensor" and rename column "body" as "value" respectively in three tables,
In "Ventilation sensor" table, create custom columns named "pir" and "Sensor", also rename column "airflow" as "value"
Then append four tables
Best Regards
Maggie
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
