cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
michaelsparrow Helper I
Helper I

Making a table to connect different tables

I am trying to connect 3 tables that have different 'Resources' for each value.

Table 1          Table 2                 Table 3

COO              FOO                      BOO

CAA               FAA                      BAA

CFF                FFF                       BEE

                                                  BTT

 

So in table 1 COO equates to FOO in table 2 and BOO in table 3. Table 1 CAA equates to FAA in table 2 and BAA in table 3. However, Table 1 CFF equates to FFF in table 2 and BEE and BTT in table 3.

I can connect Table 1 and Table 2 easily by making a new table that has these values in different columns and then making relationships between the original table and the new table.

The problem is because CFF=FFF=BEE and BTT. I don't know how to put BEE and BTT in the linking table to connect the tables.

Any ideas?

Cheers

1 ACCEPTED SOLUTION

Accepted Solutions
SabineOussi Skilled Sharer
Skilled Sharer

Re: Making a table to connect different tables

Of course you can!

If you need them to stay separate, forget about the merging and the new table you mentioned, and just do this

Untitled.png

View solution in original post

7 REPLIES 7
Microsoft
Microsoft

Re: Making a table to connect different tables

Hi @michaelsparrow

 

Could you ssplit the data into two columns? 

 

So have Table1 as follows and just link up on Col2?

 

Col1 , Col2
--------------
C ,OO
C ,AA
C ,FFF

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

michaelsparrow Helper I
Helper I

Re: Making a table to connect different tables

Hi Phil,

 

That still doesn't solve the problem that there are 3 entities in Table 1, 3 in Table 2 and 4 in table 3.

 

Microsoft
Microsoft

Re: Making a table to connect different tables

Could you create a new column in Table 3 to combine the two values into just 1 value?

 

So still split as suggested, but create a simple formula to say for Table three

 

where values are X & Y, then just make then X for the purpose of linking


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

michaelsparrow Helper I
Helper I

Re: Making a table to connect different tables

ok sounds good, so how do i combine the values of BEE ad BTT? For example:

BOO    4

BCC     6

BEE      7

BTT     11

 

How would I combine BEE and BTT?

SabineOussi Skilled Sharer
Skilled Sharer

Re: Making a table to connect different tables

Does query merging work in your case?

 

Instead of splitting, you can add a new conditional column for each table as follows:

if column end  with oo then oo, aa then aa, ff then ff

Do this for tables 1 and 2

For table 3 do the following:

if column ends with oo then oo, aa then aa, ee then ff, tt then ff

 

Merge tables 1 and 2 on that new custom column, let's name it table 4.

Merge tables 4 and 3 also on the new custom column, let's name it table 5.

 

You can then remove the new custom columns from table 5 and end up with this

Capture.PNG

 

I am not sure that is exactly what you are asking for and if your real data is actually more complex.

Let me know how it goes!

michaelsparrow Helper I
Helper I

Re: Making a table to connect different tables

Hi @SabineOussi,

 

Thanks for the response.

Could I just add a table with those values (enter data) and then link the tables together with relationships rather than merging everything?

 

Cheers

SabineOussi Skilled Sharer
Skilled Sharer

Re: Making a table to connect different tables

Of course you can!

If you need them to stay separate, forget about the merging and the new table you mentioned, and just do this

Untitled.png

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors