PowerBi Merging/Relationships

Hi Reuben,

Thank you for the forum question.

Here’s a clear, practical way to design this in Power BI so you can reliably map Airline → Aircraft → Service → Finance Rate, even when:
• Raw operational data comes from SQL Server (DirectQuery)
• Finance rates + mapping live in a local Excel file
________________________________________
Recommended Architecture
1. Build a Mapping Table in Excel
Since the terms in the raw feed differ from finance terminology, you need a master mapping table.
Your Excel workbook should contain something like:
Table 1: Mapping
Raw Airline Finance Airline Raw Aircraft Finance Aircraft Raw Service Finance Service
Table 2: Rates
Finance Airline Finance Aircraft Finance Service Rate
This becomes the "translation layer" between SQL data and finance terms.
________________________________________
2. Import Excel Tables into Power BI (Import Mode)
Keep Excel as Import — DirectQuery + Excel works fine as long as Excel is import.
• In Power BI: Home → Get Data → Excel → Select workbook
• Load the two named tables:
o Mappings
o FinanceRates
________________________________________
3. Connect Excel Mapping to SQL Raw Data
Your SQL DirectQuery table likely contains:
• Airline (raw name)
• Aircraft type (raw name)
• Service type (raw name)
• Job count / Jobs completed
In Model View, link:
SQL.RawAirline → Mappings.RawAirline
SQL.RawAircraft → Mappings.RawAircraft
SQL.RawService → Mappings.RawService
Then link:
Mappings.FinanceAirline → FinanceRates.FinanceAirline
Mappings.FinanceAircraft → FinanceRates.FinanceAircraft
Mappings.FinanceService → FinanceRates.FinanceService
This builds a clean, fully mapped relationship chain.
________________________________________
4. Build a Measure to Calculate Revenue
Example DAX:
Total Revenue =
SUMX (
SUMMARIZE (
JobsRaw, -- SQL table
JobsRaw[JobID],
"JobRate", RELATED(FinanceRates[Rate])
),
JobsRaw[JobsCompleted] * [JobRate]
)
You can simplify if you only have 1 job per row.
________________________________________
5. Build Your Dashboard
Now you can create visuals such as:
• Jobs completed per airline
• Revenue per airline
• Revenue per service
• Receivables vs activity
And Power BI will:
1. Read job counts live from SQL
2. Map raw values to finance terminology via Excel
3. Apply finance rates
4. Calculate revenue dynamically
________________________________________
Why this approach works best
Requirement Why this design fits
SQL is DirectQuery You avoid mixing modes on the main fact table
Excel contains rates Import allows full modeling + joining
Mapping is needed Excel mapping is flexible and easy to maintain
Finance needs full control They can update the Excel file without touching SQL
________________________________________
Alternative (More Advanced) Options
If you want something more scalable:
Option 1 – Move the mapping into SQL Server
Finance can still update via Excel → automated import into SQL.
Option 2 – Use Power BI Dataflows
Upload Excel to OneDrive/SharePoint → it refreshes automatically.
Option 3 – Use Composite Models (Dual / DirectQuery for PBI datasets)
If performance becomes critical.
I can help you choose one if you tell me:
• Where the Excel file is stored (local? SharePoint? OneDrive?)
• Whether finance wants to update it frequently
• The volume of SQL data
________________________________________



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector