Merging tables/creating relationships

[Anonymous]

Hi All,

I'm hoping someone may be able to help me here...I have one table that has mutiple rows for each date which looks something like this:

Date                Channel          Goal 1

01/01/16         Web                 5

01/01/16         Mobile             6

01/01/16         Offline             8

01/01/16         Tablet              2

I have another similar table from a differnt data source with a second goal and some offline data.

Date                Channel          Goal 2    Visits

01/01/16         Web                 5            300

01/01/16         Mobile             6            400 

01/01/16         Offline             8            500

01/01/16         Tablet              2            400

I would like to combine the data and wondering if this is possible in Power BI? 

Thanks,

Chris

[Anonymous]

Hi there,

Let me please join this conversation as I think I'm still missing the answer to the original question. What's the difference if I merge queries VS build relationship between two tables?

When I merge two queries (which on my mind should link two tables), there's no new link appearing in the visual relationship section and no result of this merge can be seen.

[BhaveshPatel]

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. 

[Anonymous]

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!  

[Anonymous]

Thanks a lot for your prompt response Bhavesh! Your answer helped me in my particular case.
However, my general concern remains. It looks like you cannot manage left join effectively with the relationships. For example, if you have two tables Sales and SelectedOutlets, you cannot build relationship between them so that the result displays sales for those outlets which are in the SelectedOutlets table and others which exist only in the Sales. Right?

[BhaveshPatel]

Hi @Anonymous

 The relationship view (One to Many Relationship Type) is equivalent to Left Outer Join in SQL World.

If you are looking to understand the relationships and cross filter view in Power BI, Please go through the below video resource.

https://www.youtube.com/watch?v=8ybsChtuZoY

and Microsoft Documentation:

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

[BhaveshPatel · Accepted solution]

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

[Anonymous]

Thanks Bhavesh. If the goal columns are differnt in the two tables, so one is sign ups, the other is applications, could I merge by selecting just date and channel?

[BhaveshPatel]

Yes You can.