How to join tables and retain all columns from both tables.

[Fourthsky]

I have been struggling with this issue for a couple of weeks now and can't seem to model the data in PowerBi the way I need to.

I am using PowerBi desktop and my data is coming from Direct Query. There is no schema available for the database, but there are foreign keys that will act as filters between various tables automatically. I don't have a map of those either. My ability to create direct active relationships between existing tables is very limited as it throws errors most of the time.

My end goal is to get the T3.data column in to Table 1. I don't actually need the data from Table 2, its just the only table I have that contains a common column with both Table 1 and Table 3. There is nothing in common between Table 1 and Table 3.

1. I can create a working relationship between Table 1 and Table 2 using T2.id.

2. I can create a working relationship between Table 2 and Table 3 using T3.id

3. I have tried using Dimension tables but I still can't map together all three tables.

4. I have tried a Power Query 'Merge query as new' on Table 2 and Table 3, Left Join on Table 3 using T3.id so that T3.data is preserved as a column. However, I lose T2.id in this case as the Left Join (or any join) in Power Query does not bring all columns from both tables, only the Left table. So I can not make a relationship between Table 1 and the Merge table afterwards. None of the available Joins, such as Outer Join, seem to solve this issue of retaining columns.

Table 1 is where all the data is that I'm using in a visual that I need to export to .csv for another department. So my goal is to get T3.data into Table 1 as part of this export, relating properly to T1.id.

Any help is appreciated.

I have 3 tables:

Table 1

T1.id	T2.id
1	1
2	null
3	3

Table 2

T2.id	T3.id
1	1
null	2
3	3

Table 3

T3.id	T3.data
1	234
2	345
3	567

[v-yalanwu-msft]

Hi, @Fourthsky ;

According to your simple file,  you could merge in power query.

1.merge table1 and table2.

2.expand it.

3.merge table1 and table3.

4.expand it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglIyArrzQnB8wxBnKMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [T1.id = _t, T2.id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"T1.id", Int64.Type}, {"T2.id", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"T2.id"}, Table2, {"T2.id"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"T3.id"}, {"Table2.T3.id"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Table2", {"Table2.T3.id"}, Table3, {"T3.id"}, "Table3", JoinKind.LeftOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(#"Merged Queries1", "Table3", {"T3.data"}, {"Table3.T3.data"})
in
    #"Expanded Table3"

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MarkLaf]

As the error message states, you should be able to get active relationships working if you specify a filter direction instead of using bi-directional filter. There are only a few scenarios where bi-directional filters are truly appropriate and they should generally be avoided as a best practice.

I think this should work. I loaded your original dummy tables into a dataflow with enhanced compute engine to connect with Direct Query.

1. Set a consistent overall direction (i.e. 'Table 1' -> 'Table 2' -> 'Table 3' OR 'Table 1' <- 'Table 2' <- 'Table 3')

2. Now you can drag and drop in visuals:

3. Or generate a flat table:

[Fourthsky]

@MarkLaf Thanks for the reply. See my attempt with the first two tables below. What you see pictured is the entirety of my relational diagram. I'm beginning to think there are underlying relationships in the DB interfering with everything I try to do, even though the DB admins have told me there aren't.

If I could expand the merge function to include all columns in two tables as @Jeanxyz mentioned below I think my issue would be resolved. I'd merge tables 2 and 3 in their entirety and then use a relationship between table 1 and the merge table.

Any idea if I can expand the merge to include 2 tables? My pic below shows my merge and I see no way to do that.

[MarkLaf · Accepted solution]

That's interesting and I think tracks with this sqlbi article that mentions when setting up relationships between DQ tables in the same data source, PBI will push management of the relationship to that data source, which means existing relationships in the data source could potentially interfere with ad hoc relationships you may want to leverage in your pbi model. Maybe (no direct experience here so just a guess).

First, in case you are just missing the next step after clicking through the merge window you already shared a few screen captures of, here is a quick explanation on expanding the newly joined columns as the next step: https://docs.microsoft.com/en-us/power-query/merge-queries-overview#expand-or-aggregate-the-new-merg...

So, to be clear, after your first merge:

1. Expand the columns you need from the joined table as described in link above

2. Start another merge through the ui and complete same steps as before but with next table's FK/PK

3. Expand the new nested join column to get final column you want

Second, in case the above isn't helpful, perhaps the following may be a workaround to the data source relationship issue. I'm not totally sure this would work, but you could try setting up inactive relationships and then do a combo REMOVEFILTERS and USERELATIONSHIP to a) temoporarily clear any underlying relationships that may interfere with your model, and b) temporarily use the inactive relationship. E.g.

Model:

DAX for a table:

Result = 
GENERATE(
    'Table 1' ,
    CALCULATETABLE( 
        'Table 3',
        REMOVEFILTERS( 'Table 2' ),
        REMOVEFILTERS( 'Table 3' ),
        USERELATIONSHIP( 'Table 1'[T2.id], 'Table 2'[T2.id] ), 
        USERELATIONSHIP( 'Table 2'[T3.id], 'Table 3'[T3.id] )
    )
)

Result:

[Fourthsky]

Thanks I needed that extra bit of info to expand the merge. Despite my other data issues, this solves my immediate problem.

Thanks again!

[Jeanxyz]

strange, when you merge two tables, you should be able to bring all columns from table2 to table 1. Did you expand table 2 after merging?

[Fourthsky]

@Jeanxyz Can you elaborate on how to expand the merge? I do believe that is the crux of my problem. Here is a merge between two tables. How do I get all of the columns?

Further to this, additional Join types only mention rows.

[amitchandak]

@Fourthsky , You need a common dimension table. either in DB of in power bi

distinct(union(distinct(Table1[ID]), distinct(Table2[ID]), distinct(Table2[ID]) ))

use with all three tables and use

refer -https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...

[Fourthsky]

@amitchandak Thanks for the reply. I tried what you said. My diagram is below.

Issues are that I can make only one of those relationships active. The other two throw an error like this when I try to make them active:

This is a very confusing problem for me. The errors are referring to relationships between tables that are not in this diagram. As I said before this is Direct Query and I have no relationship schema for the database itself nor any access to it.

Is this an insurmountable problem due to Direct Query? Would I have this issue if I was using Import Tables?