Why these two patterns cause migration pain
Tableau authors often solve modeling problems inside the workbook:
- Custom SQL wraps joins, filters, and reshaping in a query object used as a table
- Data blends stitch a secondary source to a primary source at viz time using linking fields
Power BI pushes you toward an explicit semantic model: Power Query (or a warehouse) shapes tables; relationships (and occasionally measures like TREATAS) express how tables filter each other.
If you paste Custom SQL unchanged into every model and try to emulate blends with ambiguous many-to-many relationships, reports become slow, incorrect, or unmaintainable.
Custom SQL in Tableau
What it is
A Custom SQL data source (or table) is a SQL statement Tableau sends to the database. It may:
- Select a subset of columns
- Join multiple tables
- Apply business filters
- Union periods or systems
- Call functions specific to that database dialect
Where it should go in Microsoft
| Custom SQL shape | Better home |
|---|---|
Thin SELECT / rename / filter |
Power Query or a warehouse view |
| Multi-table join used by many reports | Warehouse / lakehouse view or dbt model |
| Heavy transforms, window functions | Upstream SQL or Spark/notebook job |
| Source-specific dialect tricks | Keep in the database layer; expose a stable view |
Rule of thumb: if more than one report needs it, promote it out of report-local Power Query into a shared database object or dataflow.
How to migrate a Custom SQL object
- Capture the SQL text and the database it runs against
- Identify grain (one row per what?) and primary keys
- Decide shared view vs model-local query
- Recreate as a view / dataflow entity / Power Query step
- Bring into the semantic model as a table, then relate it—do not leave it as a black-box dual to half the model
- Regression-test row counts and a few group-by totals against Tableau
Watch for: parameters embedded in Custom SQL, initial SQL, and session constants that Power Query will not inherit automatically.
Data blends in Tableau
What a blend really is
A blend keeps two data sources separate. At visualize time, Tableau queries the primary source, then queries the secondary source at the linking field grain, and stitches results—behavior closer to a left post-aggregate lookup than to a relational join in one database.
Consequences:
- Secondary measures often aggregate at the link grain, then appear next to primary detail
- Linking fields must match after aggregation
- Performance and correctness surprise people who think “blend ≈ join”
Power BI equivalents
| Blend use case | Power BI approach |
|---|---|
| Two tables in one database | Single model with relationships (prefer star schema) |
| Dimension lookup from a second DB | Bring both into one model (Import) or use a shared warehouse mart |
| Occasional secondary metric at high grain | Pre-aggregate secondary table to link grain; relate 1:* |
| True cross-system federated blend | Land both in Fabric/warehouse first; avoid runtime federation if possible |
| “Lookup” measure without physical merge | Relationship + measures; sometimes TREATAS / temporary filter patterns |
Avoid creating bi-directional many-to-many relationships just to mimic a blend. Fix grain instead.
How to replace a blend
- Name the primary and secondary sources and the linking fields
- State the intended grain of the visual (“one row per Order”, “per Customer-Month”)
- Build tables at that grain in Power Query or SQL
- Create relationships with correct cardinality (*:1 toward dimensions)
- Rewrite measures so secondary facts are not double-counted
- Validate with a visual that previously relied on the blend
Program approach
- Inventory — Every Custom SQL object and every blend (primary/secondary/links) per workbook
- Cluster — Duplicate SQL across workbooks → one shared view
- Remodel waves — Platform SQL/blends before long-tail workbooks
- Ban silent paste — Code review for Custom SQL dropped raw into PBIX without an owner
Common pitfalls
- One giant Custom SQL “universe” that no one dares edit
- Blends used because authors lacked permission to join in the database—migration is the moment to fix access and modeling
- Many-to-many relationships “to make the numbers show up”
- Comparing Tableau blend totals to Power BI joined detail without aligning grain
Checklist
- [ ] Custom SQL inventory with grain and consumers
- [ ] Shared views/dataflows created for reused SQL
- [ ] Blend inventory with link fields and intent
- [ ] Relationships redesigned at correct grain
- [ ] Measures tested for double-counting
- [ ] Performance checked on production volumes
Related topics
- Published Tableau data sources vs Power BI semantic models
- Tableau extracts vs Power BI Import and DirectQuery
- Tableau Prep flows vs Power BI dataflows
- Validating Tableau to Power BI migrations
