Hello!
This will be long...
At first, my English is bad, I am very new to PBI and also in something like data science so please be patient and try to detect some problems in my solution and describe it with more details. Thank you.
So... I have 2 AZURE TABLES:
(1) Internal Data:
- Date time
- Temperature
- Humidity
(2) External Data:
- Date time
- Temperature
- Humidity
Problem is that in table (1) are about 4 times more data. Because table (1) obtaining data about every 30 seconds and table (2) obtaining data every 2 minutes.
I rounded the date times of both tables to 15 seconds. (NOT SURE IF NECESSARY?)
Then I created MANY-TO-MANY relationship between tables based on that rounded date time. (because there can also happens state where there came 2 rows of data at same time, so IS M-T-M GOOD CHOICE?)
And now... I need to SUBTRACT temperatures and humidities for new columns: TEMPERATURE and HUMIDITY DIFFERENCE. Count of new rows should be exact as INTERNAL table rows count. Because there are more internal data than external, I need to calculate SUBTRACT of current INTERNAL data and CLOSEST EXTERNAL data. (IS THAT GOOD IDEAS?)
And the second question. Is there any way to mix both tables into one table, where first column will be Date time in 15 seconds intervals from 2019/2/22 00:00:00 to NOW. Other columns I want to chose from that tables... And rows will be filled from both tables based on date time match. And last, hardest... Other empty rows, cells should be filled from with last value in that column. If I will have this "big" table then my first problem should be easy solved, but the best for me will be when I will know both solutions and then choose.
This is part of my Balechor's work and I really stuck there. I don't know ho to do almost all of this. Thank you very much for every answer.
