Forum Discussion
Combine Two Visual Tables
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 |
11 Replies
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.
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.Hi! Anonymous
I didn't understand your requirement. What are you trying to achieve?
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
Result2. 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,ODI 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
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.
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 visualYou 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
