Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Merging Tables

I currently have 4 tables that would be considered siblings because they all have container_id & truck_id. The rest of the columns vary among the 4 tables. I attempted to make a relationship between ...
  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    Anonymous

     

    I think you can duplicate the four tables in Query Editor and only keep truck_id column for these duplicate tables. Then append these four duplicate tables and remove duplicate rows. Now you will have a table with only one column with unique truck_id. We should be able to create the relationship like below.

    If you want the truck_id and container_id to be the key, you can combine truck_id and container_id into a single column as Vvelarde said.

     

    Best Regards,

    Herbert

kcantor's avatar
kcantor
Community Champion
10 years ago

Anonymous If the tables all have different columns but share container_id and truck-id you would want to merge instead of append. What other data do the tables contain? Can you provide a snapshot of the tables?

As an alternative, you could create a lookup table with container and truck id to make relationships possible. See this blog for how:

http://www.powerpivotpro.com/2014/03/creating-dynamic-lookup-tables-with-unique-values-using-power-query-instead-of-a-database/

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Here is sample of the tables. I am attempting a full outer join megre on two of the tables with container id selected on both, and its going slow. This table has about 9 million rows and when it hits like 160k it just stops on the full outer join.