Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
PowerOper
Frequent Visitor

Merge Two Tables of Different Measurements by TIME and ID into single table?

I have two tables, each are similar except that they contain different types of measured values, for example one contains pressure data, and the other has temperature data.

 

I would like create a new table that merges the two tables so that I have the two measurements, pressure and temperature, in the same table for each id and time.

 

As an example, here are the two source tables I would like to merge. My apologies for the formatting. The forum site isn't loading properly for me.

 

PRESSURE_TABLE                                                           TEMPERATURE_TABLE

id      time                        pressure                                       id      time                        temperature
A      2019-12-13 1:00     5000                                             A      2019-12-13 1:00     12

B      2019-12-13 1:00     5200                                             B      2019-12-13 1:00     15

 

The resulting table after the merge would look like this:

id      time                        pressure         temperature
A      2019-12-13 1:00     5000               12
B      2019-12-13 1:00     5200               15

8 REPLIES 8
az38
Community Champion
Community Champion

Hi @PowerOper 

if i understand correct it shoud be enough to add calculated column to table pressure or temperature like this:

Temperature = lookupvalue('TEMPERATURE_TABLE'[temperature];
'TEMPERATURE_TABLE'[id];[id]
'TEMPERATURE_TABLE'[time];[time]
)

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Thanks @az38. I've tried that but it returns #ERROR with the error message "A table of multiple values was supplied where a single value was expected."
az38
Community Champion
Community Champion

@PowerOper 

it means that you have more then one row that satisfied both of your conditions.

you need to check data or define another rule

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 When I perform the lookup manually, I don't see duplicates or multiple rows. I do have the 2 tables joined to another dates table on Timestamp, maybe that is causing the issue. I generated this third table as a distinct union of the timestamp fields from the 2 tables. Must be something to do with my relationships. Thanks

Yeah I'm probably going about this the wrong way. The two tables are actually derived from another table, so perhaps I should be trying to manipulate that one instead. The source table has columns [Timestamp], [Value], [Measured Name], [ID] and in the Measured Name field it has "Temperature" or "Pressure" to describe what value it is. I had created two separate tables to derive Temperature and Pressure by Timestamp and ID. What I really want to do probably is just pivot the original table so that the values are grouped for [Measured Name] columns. Is there a way to PIVOT with DAX? I'm going to google that @az38

OK well this was easy. I just had to pivot the [Measured Name] column in the original M Query. I'm not sure what I didnt' think of that before. My apologies Cheers
az38
Community Champion
Community Champion

@PowerOper 

thats great! good luck!

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
az38
Community Champion
Community Champion

so, @PowerOper we need to look at your data model to help you

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors