DATA GOBLINS DATAFLOW CHECKLIST

Version X.X - Update-in-progress: March 2024

Use the below interactive checklist to follow-up on your Power BI Dataflow.
Many of these may also apply to any Power Query tool:

Click the question mark (?) for a link to a reference document. This will be learn.microsoft.com documentation; if none are available, this may be a Data Goblins article, or another blog post reference.

Dataflow Design

Check if you need a dataflow - does it fit the use-cases of single truth, re-used transformation logic? ?

Check if you can or should use the 'Common Data Model' ?

Check the 'Considerations & Limitations' in the Microsoft guidance documentation ?

Browse the starting data & define your desired 'end-point'; what transformation steps are needed? ?

Map dependencies; define the needs for each downstream point-of-consumption to avoid over-transformation ?

Plan where the Dataflow should live (which workspace - i.e. workspace specific for dataflows?) ?

Building a Dataflow

Apply a parameterized, early filter to limit data while building ?

Parameterize repeated strings, filters and data source information ?

Push transformations to the upstream data source where feasible ?

Remove unnecessary data; delete unneeded columns & aggregate unneded detail ?

Ensure query folding is maintained as long as possible ?

Avoid merges & appends; save 'expensive' steps that read full data for later ?

Follow Power Query best practices from Microsoft ?

Follow Dataflows best practices from Microsoft ?

Check data types & decimal precision ?

Disable load on intermediate data sources & functions ?

Design & add error handling on 'sensitive' steps where the query could fail ?

Optimizing a Dataflow

Reduce steps & complexity as much as feasible; tweak steps to ensure folding ?

Configure incremental refresh if you are on a premium capacity ?

Try advanced code-authoring approaches, editing the Advanced Editor query script ?

If connecting to Excel files, convert to .csv instead of .xlsx if feasible ?

Avoid query references to optimize if not needed, as it hits the source multiple times ?

Carefully test Table.Buffer and List.Buffer approaches if the query is not folding ?

If connecting to parquet files, understand PQ will not do predicate pushdown, etc. ?

Experiment with different 'hot'/'cold' partition strategies if no incremental refresh

Try different approaches for multiple find & replace, i.e. add conditional column

Configure enhanced compute engine (Premium) ?

Readying a Dataflow for Deployment & Handover

Define clearly who owns & updates the dataflow

Rename steps to concise, clear & logical names ?

Add helpful, clarifying comments to each step ?

Format the code in the advanced editor using VS Code's "Power Query" extension ?

Add a description to the dataflow and dataflow entity (table)

Configure scheduled refresh & endorsement ?

DATAFLOW USER TRAINING CHECKLIST

Training users takes time and effort, and the approach differs for every person being trained. Listed below are some elements of Power BI Dataflows that one should consider when training them when, why & how to use a Dataflow, effectively.

Train Dataflow Users

Advantages of dataflows: centralizing data/logic, scaling performance, etc. ?

Connecting to dataflows from Power BI or Excel ?

Which dataflow to use (i.e. certified, promoted) for what data ?

What are the Power Query best practices ?

What are the Dataflows best practices ?

Limitations & considerations of dataflows they can use or create ?

DOCUMENTATION/SUPPORT CHECKLIST

Documentation varies from project-to-project and team-to-team, but below are some things you can consider documenting for your Power BI Dataflow. The main thing you want to document is the lineage; what upstream sources are consumed & combined, and what downstream dependencies are consuming the dataflow.

Documentation for Power BI Dataflows

Basic Info: Name, SPOC, Location, Workspaces...

Purpose: Business questions/problems the model addresses

User segments: Intended audience and tools used

Complexity: atypical properties/features

Data sources: Type, when updated...

Security and Access: Who has build permissions...

Manual interventions & justifications (if any)

Typical refresh time; how long does it take to refresh?

Refresh schedule; what frequency does the Dataflow update? When do upstream sources update?

Schema of each entity / table; what columns are in the dataflow?

Volume of each entity / table; how many rows do they have? ?

Custom functions; what advanced M code has been authored? Can it be re-used?

Shared expressions; which expressions have disabled load and why?

If the Dataflow uses other subscriptions - Premium or AI features, for example

'Common Data Model' mapping ?

Sensitivity label / DLP policies; if it contains PII or restricted information ?

Lineage & Dependencies; what content are consuming the dataflow? ?