Combine Two Visual Tables

[Anonymous]

I've been given a report that contains two tables in Power Bi.  They're identical fields except for one column from a different table.  My task is to combine both visuals into a single visual.  The end user is having to export both tables to excel, then copy/paste them together.  

I've tried several methods, including merging tables and unpivoting columns, and using SUMMARIZECOLUMNS to try and create the tables and merge them, but have had no luck.  

The issue is that the data is coming from Dynamics 365, so there's dozens of tables with a tangled web of relationships.  Trying to merge two tables into another and unpivot lead to relationship issues (Having two references in one column and trying to link it back to their respective tables causes relationship already exists errors) , and I ran a powerful desktop out of memory trying to get SUMMARIZECOLUMNS to work.  Any ideas would be greatly appreciated.  

The data is sensitive, so I've created an example below (The real tables have 18 columns).  Assume all columns are from different tables.

Table 1: 

Data 1	Data 2	Data 3	Data 4
Data1A	Data2A	Data3A	Data4A
Data1B	Data2B	Data3B	Data4B

Table 2:

Data 1	Data 5	Data 3	Data 4
Data1C	Data5A	Data3C	Data4C
Data1D	Data5B	Data3D	Data4D

Desired Result:

Data 1	Data Combined	Data 3	Data 4
Data1A	Data2A	Data3A	Data4A
Data1B	Data2B	Data3B	Data4B
Data1C	Data5A	Data3C	Data4C
Data1D	Data5B	Data3D	Data4D

[PaulDBrown]

I agree with @AnkitKukreja that the simplest solution is to append the tables. You don't even have to change the model; simply create duplicates of the tables in Power Query and append them.

Having said that, here is an alternative.

Create a Dimension table for Data 1 and create the relevant relationships:

Then create measures following this pattern:

Data Combined =
VAR _T1 = VALUES(Table1[Data 2])
RETURN
IF(
    COUNTROWS(
        _T1) = 1,
        MAX(Table1[Data 2]), MAX(Table2[Data 5]))

To get:

I've attached the sample PBIX file 

If it is still unsolved, please provide a sample PBIX file

[Anonymous]

Unfortunately this won't work because Table 1 and Table 2 are visual tables, not existing tables from the data source.  Each column is basically from different tables within the data.  

I tried replicating this by using SUMMARIZECOLUMNS and then appending the results, but there are so many relationships it runs the computer out of memory before finishing.  

The raw data is from Dynamics 365 and there are dozens of connections between the different tables.  

[PaulDBrown]

Ok. So are the datapoints actual columns or measures? 
I take it there is a common field between the tables: Data 1, which can be appended into a dimension table as in the model I posted?
If so, you can alter the measures to include the measures you already have along the lines of:

Combined =
VAR _T1 =
    VALUES ( Table1[Data 4] )
RETURN
    IF ( COUNTROWS ( _T1 ) = 1, "[Measure 1]", "[measure 2]" )
// The [Measures] are in between " " to be able to show as text in the visual

You say the tables are visuals. So how are they constructed? 

Otherwise we would need to play with an actual dummy PBIX file to be able to try to help further

[Anonymous]

The datapoints are columns.  Table 1 and Table 2 are constructed by using the table visual.  Nothing fancy, each column from its respective table is dropped in.  The problem is that Data 2 and Data 5 are columns from two entirely different tables, but the way the complex relationships work, pull the same data.  

The data I'm using is sensitive so I unfortunately can't post specific examples, which I know is a pain when trying to figure the problem out.  

[PaulDBrown]

If you are pulling actual fields from tables to construct each visual, you could create mockup tables in Excel with the column structures for each table used. As an alternative, create a dummy PBIX without any values, filtering down rows and replacing names, all dowable in Power Query.

But up to you.

[OzkanDhont]

Hi @Anonymous !

Correct me if I'm wrong, the second column is either 'Data 2' or 'Data 5' while the other columns are the same?

How do you determine whether it's 'Data 2' or 'Data 5'?

Perhaps following solutions could give you the desired result:

1. You could use the switch function to return either 'Data 2' or 'Data 5' depending on a condition.

Measure = 
VAR Result = SWITCH(TRUE(),  
                    Type=1 ,[Data 2],
                    Type=2 ,[Data 5]
                    )
RETURN 
Result

2. Or if 'Data 2' and 'Data 5' don't overlap you could just add them to eachother in Power Query or Dax.

Hope it helped!

Kind regards,

OD

[Anonymous]

Data 2 and Data 5 are different columns in two different tables.  The report makes two visual tables to capture all of the data, and I'm trying to combine it all into one table.  

[AnkitKukreja]

Hi! @Anonymous 

Appending them would be best in my opinion. Please delete the relationship among these tables and then Append them. As I believe after appending them as a single table you won't need the relationship between them.

[Anonymous]

Hello and thank you for the reply! 

Just to clarify, the two tables don't exist as tables within PowerBi, they're table visualizations created from existing tables.  I've tried creating them as tables and appending them using SUMMARIZETABLES, but it runs my computer out of memory - I'm assuming because of their several relationships.  

[AnkitKukreja]

Hi! @Anonymous 

I didn't understand your requirement. What are you trying to achieve?

[Anonymous]

I have a Power Bi report that pulls data from Dynamics 365.  It has two almost identical visual tables that the end user wants to combine.  The only difference is a single column, but it comes from a completely different table from Dynamics 365.  

Making the separate visual tables is simple, but I can't figure out how to combine them into a single visual.