Hi All,
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.
C_ID | U_ID | Name |
003U000001OtAWDIA3 | 0054O00000ALE90 | John Doe |
003U000001kHRC5IAO | 0054O00000ALE90 | Jane Johnson |
003U000001OtH9mIAF | 0030B000025JZs0QAG | Matt Stewart |
Table1
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.
C_ID | Name | Title | Division | Demo |
003U000001OtAWDIA3 | John Doe | D | 1 | Presentation |
003U000001kHRC5IAO | Jane Johnson | M | 1 | Call |
003U000001OtH9mIAF | Matt Stewart | D | 3 | Call |
Table2
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 |
Table3
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 |
Table4
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:
C_ID | Name | Title | Division | C Type | Amount ($) | Demo |
003U000001OtAWDIA3 | John Doe | D | 1 | DC-F | USD 2,000 | 0 |
003U000001OtAWDIA3 | John Doe | D | 1 | SC-F | USD 4,500 | 0 |
003U000001kHRC5IAO | Jane Johnson | M | 1 | DC-C | USD 6,500 | 0 |
003U000001OtAWDIA3 | John Doe | D | 1 | 0 | 0 | Presentation |
003U000001kHRC5IAO | Jane Johnson | M | 1 | 0 | 0 | Call |
Table5: Desired Result in PowerBI
Thank you!
