Forum Discussion
Merging tables/creating relationships
Hi Anonymous
Yes Chris, You can use Query Editor, Merge Queries and Left outer join to combine your tables. Please see the screenshot.
Merge, Left Outer Join and Expand, Select Visits
Merging Queries in the query editor is happening in the underlying ETL engine of Power Query and it is expected to be used for ETL kind of scenarios.
Creating and Building Relationships is happening in the vertipaq in memory engine of Power BI. This is used to create a data model and builing complex aggregations and calculations for supporting analytical workloads.
Both of these engines (Power Query Mash up Engine and Vertipaq In memory engine)are combined in Power BI to provide ETL workloads as well as analytical aggregation calculations.
Relationships are supported via Primary Key Relationships and is a feature of Vertipaq Engine. If your tables does not have unique keys to join on. Creating relationships on the multiple columns in not currently supported in Vertipaq Engine. As mentioned earlier, You have to come up with concatenated columns primary keys if you would like to join based on the mutiple columns to create unique primary - foreign keys relationships view o so called data model.
As opposite to the Vertipaq Engine, In Query Editor Power Query Mash Up Engine, You can create joins on multiple columns without creating concatenated primary foreign keys. This behaviour is somthing similar like SQL.
I hope this answers your question.
Hi Bhavesh. I direct-import 8 tables (10 attributes, 20,000 records each, mostly decimal type) from SQL Server which I then merge on datetime columns. All I do with the data is display certain attributes from each table on table and plot visuals (by the common datetime column). I do some calculations but only within tables, not across them.
As an example of whether to use Power Query ETL engine or Vertipaq, would you recommend I use merging or relationships, in my case?
Thank you!