Merging Tables

[Anonymous]

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 them but soon figured out that was not possible. Now I am trying to use the append function when I go into edit query to create a giant table with all of these columns. I understand some results will be null. I attempted to join them but received error. Due to sata types, so then I went to each table and tried changing all columns to text but it wouldn't let me? I am confused and need help on how to go about completing this. The most recetn error I received when trying to change the column data type was HRESULT: 0X80040E4E.

[v-haibl-msft · Accepted solution]

@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

[Anonymous]

That makes sense but question how will I be able to pull all that data into a single column. This table has 3 million+ rows and thats from one table alone. Is there a way I can formulate the table within Power BI using sql or will it have to be done before importing into power bi from informix?

[kcantor]

@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]

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. 

[wonga]

@Anonymous Instead of merging these tables together, couldn't you instead establish relationships between the tables? If your goal is to make visuals that use data from multiple tables, establishing relationships is the best way to go about it. Power BI isn't supposed to be used for merging tables that have an exorbintant  amount of rows.

EDIT: What problem are you encountering when trying to establish the relationship(s)? Is it saying the related column isn't specific enough or something?

[Anonymous]

Yes it says that one of the columns doesn't have a unique value. Which I understand to be because they are all sibling tables with no parent.

[Vvelarde]

You can create a Unique Identifier (Union of Container ID & Truck_ID) in each table  and relationship all the tables.