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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wainnoce
Helper II
Helper II

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 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.

1 ACCEPTED SOLUTION
v-haibl-msft
Employee
Employee

@wainnoce

 

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.

Merging Tables_1.jpg

 

Best Regards,

Herbert

View solution in original post

7 REPLIES 7
v-haibl-msft
Employee
Employee

@wainnoce

 

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.

Merging Tables_1.jpg

 

Best Regards,

Herbert

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
Community Champion
Community Champion

@wainnoce 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-q...





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Example.PNG

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
Continued Contributor
Continued Contributor

@wainnoce 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?

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.

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

 

 




Lima - Peru

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.