Combining Multiple Tables Using a Unique ID Present in All Tables

Question

Hi All,&nbsp;I need to combine four separate tables, all of them being loaded into BI as excel data source files, into one table on a unique ID present in all tables. Table1 is the “master table” with a row for each unique ID (C_ID), another non-unique ID (U_ID), and the Name associated with C_ID.&nbsp;C_IDU_IDName003U000001OtAWDIA30054O00000ALE90John Doe003U000001kHRC5IAO0054O00000ALE90Jane Johnson003U000001OtH9mIAF0030B000025JZs0QAGMatt Stewart&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Table1&nbsp;The other three tables (Table2, Table3, Table4) all have a column included for C_ID (is repeated in tables 2-3), along with shared columns Name, Title, and Division. The rest of the columns have differing values. See below examples.&nbsp;C_IDNameTitleDivisionDemo003U000001OtAWDIA3John DoeD1Presentation003U000001kHRC5IAOJane JohnsonM1Call003U000001OtH9mIAFMatt StewartD3Call&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Table2C_IDNameTitleDivisionC TypeAmount ($)003U000001OtAWDIA3John DoeD1DC-FUSD 2,000003U000001OtAWDIA3John DoeD1SC-FUSD 4,500003U000001OtH9mIAFMatt StewartM3DC-FUSD 500Table3C_IDNameTitleDivisionC TypeAmount ($)003U000001OtAWDIA3John DoeD1CS-CUSD 3,000003U000001kHRC5IAOJane JohnsonM1DC-CUSD 6,500003U000001OtH9mIAFMatt StewartD3DC-CUSD 5,000Table4&nbsp;How do I model my data on my unique ID in a way that I can combine the columns/values from tables 1,2, and 3 into a single table? Could I just append Table3 and Table4, and then join/append that appended table with Table2? Table5 is my ideal result:&nbsp;C_IDNameTitleDivisionC TypeAmount ($)Demo003U000001OtAWDIA3John DoeD1DC-FUSD 2,0000003U000001OtAWDIA3John DoeD1SC-FUSD 4,5000003U000001kHRC5IAOJane JohnsonM1DC-CUSD 6,5000003U000001OtAWDIA3John DoeD100Presentation003U000001kHRC5IAOJane JohnsonM100CallTable5: Desired Result in PowerBI&nbsp;Thank you!

anonymous · Accepted Answer

use the merge query capability... data from table1&amp;2 merged to create Temp2, then merge Temp2 &amp; table 3... and so on.

Forum Discussion

Combining Multiple Tables Using a Unique ID Present in All Tables

2 Replies

Related content

Power Query Combine Multiple Files From Folder

Tables combine

Combine values from multiple rows

Append/ Combine Multiple Json Files

RLS multiple combination

C_ID	U_ID	Name
003U000001OtAWDIA3	0054O00000ALE90	John Doe
003U000001kHRC5IAO	0054O00000ALE90	Jane Johnson
003U000001OtH9mIAF	0030B000025JZs0QAG	Matt Stewart

C_ID	Name	Title	Division	Demo
003U000001OtAWDIA3	John Doe	D	1	Presentation
003U000001kHRC5IAO	Jane Johnson	M	1	Call
003U000001OtH9mIAF	Matt Stewart	D	3	Call

C_ID	Name	Title	Division	C Type	Amount ($)
003U000001OtAWDIA3	John Doe	D	1	DC-F	USD 2,000
003U000001OtAWDIA3	John Doe	D	1	SC-F	USD 4,500
003U000001OtH9mIAF	Matt Stewart	M	3	DC-F	USD 500

C_ID	Name	Title	Division	C Type	Amount ($)
003U000001OtAWDIA3	John Doe	D	1	CS-C	USD 3,000
003U000001kHRC5IAO	Jane Johnson	M	1	DC-C	USD 6,500
003U000001OtH9mIAF	Matt Stewart	D	3	DC-C	USD 5,000