Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Merging tables/creating relationships

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

  • BhaveshPatel's avatar
    BhaveshPatel
    9 years ago

    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

8 Replies

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      BhaveshPatel
      Super User
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago
        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?