Forum Discussion

nataliesmiy1357's avatar
nataliesmiy1357
Helper IV
4 years ago

Merging Tables together

I was wondering if you could help me in my powerBI issue that I’m currently facing….

 

I have two tables that are not connected by a relationship (V_TM_ModulePowerBI and TBL_TEAMMEMBER_TRAINING).  I need to make one table with information from both of these tables. 

 

I need from each table:

Module PowerBI (Course name, Module id, module name, and level)

Team member training (level 1 date, level 2 date, level 3 date, level 4 date, and trainer name)

 

Both of these tables have employee id and module id.  I tried connecting dax measures that way but just failed. 

 

So for example… with the filter that’s on there right now – I need module 62254 for this individual to have a level 4 date of 9/18/2017 instructed by Bob Taylor, but what I’m getting isa 10/31/2019 completion date with Subject Matter Expert as the provider.

 

Any idea as to how I can fix this?  Attached is a screenshot of the report where it is going wrong.  Thanks in advance for your help! (PS - my company doesn't allow me to share my report due to security, so I'm not sure how to share my report to give more details)

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    nataliesmiy1357 First choice would be to create a concatenated field of your user id and module id in Power Query. Then just use a Merge step in one of your queries to merge the two queries together into a single table. Otherwise, you could create a DAX concatenated column like: [user id] & "|" & [module id] in both tables and form a relationship that way.

    • nataliesmiy1357's avatar
      nataliesmiy1357
      Helper IV
      4 years ago

      I'm not sure I understand what that means or how to write/do that in powerbi...

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        4 years ago

        nataliesmiy1357 

        Power Query

        1. In Power Query Editor, select your first query
        2. Choose Add Column, add custom column and enter the formula: [user id] & "|" & [module id]
        3. Select your second query and repeat
        4. Now, with either query selected from the Home tab choose Merge queries
        5. In the Merge queries interface choose your Custom columns
        6. Merge
        7. Find the merged column at the end of your table, expand

        DAX

        1. Go to your Date view
        2. Select your first table
        3. Create a new calculated column using the formula: Custom = [user id] & "|" & [module id]
        4. Select your second table and repeat
        5. Use Model view to create a relationship betwee the Custom columns

         

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi nataliesmiy1357 ,

    I don't quite understand your needs, what is the final result you want to get? Do you want to a "table" which include module and teammember table? If yes, you can achieve it by the following 2 methods:

    1. By creating relationship and table visual

    According to your description, there are employee id and module id fields in both teammember and module tables, why not create relationship between these two tables based on these two columns? You can refer to the following document to create the relationship.

    Create and manage relationships in Power BI Desktop

    After creating the relationship, you can create a table visual and drag the fields of these two tables you want to present into the Fields panel.

    2. Merge these two table in Power Query Editor: you can use the Merger Queries as New feature

    If the above ones can't help you get your expected result, could you please share a mockup data in module and teammember table and your expected result with backend logic and special examples base on mockup data. Thank you.

    Best Regards