Appending different columns

[Anonymous]

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!

[v-juanli-msft]

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

[Anonymous]

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.

[v-juanli-msft]

Hi @Anonymous

But it is not clear for me for the other requirements.

"see how much Airflow the ventilation sensor sends when one or more than one person is at the room",

"the average time in a 10 minutes interval"

If these above you have problem to do, I would suggest you to provide details,

eg. raise an example based on your data.

You could also open another thread to let more people see your problem and help you.

Best Regards

Maggie

[Anonymous]

@v-juanli-msft

Below, I tried to explain my question in a better way.

I have 3 body sensor tables that contain date and body values, and I have another ventilation sensor table which has a date, airflow and Pir values
They all have different values, I appended all the tables together.
The appended query has a date, airflow, body and PIR values.

The ventilation sensor sends airflow values every 10 mins and the body sensors send body values once or more than once in 10 mins.

To visualize how much airflow the ventilation sends when there are one or more people in the room at the same time, I created a calculated column with 10 mins time interval
time2 = DATE(YEAR(CombinedTable[Date]),MONTH(CombinedTable[Date]),DAY(CombinedTable[Date]))+TIME(HOUR(CombinedTable[Date]),FLOOR(MINUTE(CombinedTable[Date]),10),0)
To add the body values in 10 mins time interval I created a measure
Measure = CALCULATE(SUM(CombinedTable[Body]),IF(CombinedTable[Body] > 0,1,0),LASTNONBLANK(CombinedTable[time2],""))

when the body sensor sends value 1 it means someone is in the room, sometimes it can send values more than once in 10 mins interval when it senses a body appearance. I want to get only 1 value in 10 mins for each body sensor so when I visualize the data I can see how many people are in the room, and it shouldn't be more than 3.

For example, if one of the body sensors sends value 1 more than once in 10 mins interval I only want to take 1 of them
Below is a sample of my visualization,
It shows 5 body values because it adds them all even when it sends values more than once. but the correct value should only show 3 body values because we have 3 body sensors.

 

[v-juanli-msft]

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

[v-juanli-msft]

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

[Anonymous]

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?

[Stachu]

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