Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @Anonymous
Yes Chris, You can use Query Editor, Merge Queries and Left outer join to combine your tables. Please see the screenshot.
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.
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!
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
Hi @Anonymous
Yes Chris, You can use Query Editor, Merge Queries and Left outer join to combine your tables. Please see the screenshot.
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?
Yes You can.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |