Creating new column and connecting tables

[Tashon04ka]

Hello,

I'm newby at Power BI and trying to solve the following qestions:

There is a device which collects environmental data with high frequency (1 per second). After each session I manually export data from the device - it exports it into 4 different .txt files based on the type of data (humidity, temperature, etc.). But there is no index or ID column that could connect it.

1) How to connect the data from different files? I consider to use Time as it is a unique column, what do you think?

2) How to design the solution to assemle data from different sessions? Sessions can be done in different places and the place is important to know, but the device doesn't have this function. And you cannot add it manually as there dozens of thousands of rows.

Illustration:

Session 1. "Forest"

File "Temperature"

Date                   Time         Temperature

1 May 2020    4:01:01 PM            30

1 May 2020    4:01:02 PM            30

1 May 2020    4:01:03 PM            31

Session 1. "Forest"

File "Humidity"

Date                   Time         Humidity

1 May 2020    4:01:01 PM            70

1 May 2020    4:01:02 PM            70

1 May 2020    4:01:03 PM            69

Session 2. "Lake"

File "Temperature"

Date                   Time         Temperature

1 Apr 2020    6:01:01 AM            20

1 Apr 2020    6:01:02 AM            20

1 Apr 2020    6:01:03 AM            21

Session 2. "Lake"

File "Humidity"

Date                   Time         Humidity

1 Apr 2020    6:01:01 AM            70

1 Apr 2020    6:01:02 AM            70

1 Apr 2020    6:01:03 AM            71

Ideally I would like to have something like the table below, but struggling to incorporate location

Date                   Time         Humidity      Temperature    Location

1 Apr 2020    6:01:01 AM            70                 20                 Lake

1 Apr 2020    6:01:02 AM            70                 20                 Lake

1 Apr 2020    6:01:03 AM            71                 20                 Lake

1 May 2020    4:01:01 PM            70                30                 Forest

1 May 2020    4:01:02 PM            70                30                 Forest

1 May 2020    4:01:03 PM            69                31                 Forest

Thanks for the help!

[amitchandak · Accepted solution]

@Tashon04ka , Try if merge or append can help

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Or You can join in dax

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[Anonymous · Accepted solution]

Hi @Tashon04ka ,

Check the following steps.

1# Add custom column for each table after loading them.

2# Merge tables from session1 then expand table and rename the columns. Then do the same step with tables from session 2.

3# Append merged tables.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tashon04ka]

Thank you so much @Anonymous  - it helped

[amitchandak · Accepted solution]

@Tashon04ka , Try if merge or append can help

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Or You can join in dax

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/