I'm trying to join together two excel (or csv) files in Power BI. These files are completely different (except for the key I want to join on). They also have a different granularity.
Main File:
| Company | SalesTeam | Amount |
|---|---|---|
| 1 | 1 | 100 |
| 2 | 2 | 50 |
Sales Team:
| SalesTeam | Name | Location |
|---|---|---|
| 1 | Billy Jean | Loverville |
| 1 | Elvis | Memphis |
| 2 | Bart S. | Springfield |
So, I want to join these files together, then have a drill-through from the Main File to the Sales Team. I'm thinking it's not possible since all the tutorials expect the files to have the same basic layout - more like combining them or unioning them but not joining them in a database way.
If I can't do a "join", is there another approach I should take to be able to drill-through into some detail? I've never used Power Query before but wondering if that is an option.
In Power BI, keep the tables separate, and then create a relationship between the two based on the SalesTeam column. It is common to have different granularities and table structures in PBI that are related to one another.
It is the visualization that provides the insights, not the table/data view in PBI.