Power BI and Power Query Resources

A list of useful PBI related blog posts and docs

DAX

Dax Patterns

Dynamic M Query Parameters (preview)

Unlock the Power of Dynamic Parameter Binding for DirectQuery
a Tutorial by Artemus
Dynamic M query parameters (preview) [docs]
Power BI 2020 Feature Summary: Dynamic Parameters in DirectQuery

Office Scripts / Excel Online

Saving Power Query source code in one copy

You can copy a single query, or select them all to save all queries!
If the query has dependencies, they are automatically copied!
You can paste into another PBI report or a text editor.

VS Code: Power Query Addon

get the VS Code: Power Query Addon

Improving Report Performance

Convert Multiple visuals to a matrix and background image
alluringbi.com/2019/10/21/background-concepts-for-power-bi/
alluringbi.com/2020/08/06/consolidating-report-elements-for-improved-performance/
https://dax.tips/2019/08/05/dax-fusion/
https://data-mozart.com/vertipaq-brain-muscles-behind-power-bi/
https://data-mozart.com/tiq-part-1-how-to-destroy-your-power-bi-model-with-auto-date-time/
https://data-mozart.com/inside-vertipaq-compress-for-success/
https://data-mozart.com/how-i-speed-up-my-power-bi-report-5x/

SQL Native queries

Passing Parameters To SQL Queries With Value.NativeQuery() In Power Query And Power BI
[blog.crossjoin.co.uk] Chris shows how to use Parameterized Native SQL queries
let
    Source = Sql.Database("localhost", "Adventure Works DW"),
    Test = Value.NativeQuery(
        Source,
        "SELECT * FROM DimDate 
        WHERE EnglishMonthName=@MonthName",
        [
            MonthName = "March",
            DayName = "Tuesday"
        ]
    )
in
    Test

docs Value.NativeQuery()

Power Query Tutorials | Docs.microsoft

Performance

Guidance for using DirectQuery successfully [docs.microsoft.com]
Monitor Report Performance [docs.microsoft.com]
Use Performance Analyzer to examine report element performance [docs.microsoft.com]

Caching: Binary.Buffer, List.Buffer, Table.Buffer

Power Query, Performance, Microsoft Employee, Mashup, Engine, Quality
https://bengribaudo.com/blog/2019/12/20/4805/power-query-m-primer-part13-tables-table-think-ii

Power Query M Primer (Part 13): Tables—Table Think II
Discusses performance related to using Keys, *.buffer, Formula.Firewall, and links to Power Query team posts about performance

– how-do-i-cache-or-buffer-an-intermediate-query-result-for-repeated-use
– tablebuffer-for-cashing-intermediate-query-results-or-how-workaround-unnecessary-queries-issue
– queries-evaluation-chain

Direct Query

About using DirectQuery in Power BI Desktop [docs.microsoft.com]

Incremental Refresh

https://www.poweredsolutions.co/2020/01/19/incremental-refresh-for-files-in-a-folder-or-sharepoint-power-bi/
https://radacad.com/all-you-need-to-know-about-the-incremental-refresh-in-power-bi-load-changes-only

Data Refresh in Power BI [docs.microsoft.com]

Tracing and Diagnostics

Power Query Security: Evidence Left Behind

bengribaudo.com covers locations of caches, logs, tracing, and query diagnostics

Connectors

list of all Power platform connectors docs.microsoft.com/en-us/connectors/connector-reference/

list of all Power BI data sources docs.microsoft

Specific connectors: SQL Connector

Query Folding

Query folding guidance in Power BI Desktop docs.microsoft.com

Referenced Queries

Referencing Power Query queries [docs.microsoft.com] Explains why referenced queries run the base query multiple times

Dataflows

Best practices for dataflows [docs.microsoft.com]

Formula.Firewall errors

Videos

Add by examples works better than it should.
It dynamically displays the PowerQuery used while you’re entering examples
Alex Powers: Calculated Columns

Time Zones

https://www.poweredsolutions.co/2019/10/21/handling-different-time-zones-in-power-bi-power-query/

Web.Contents

https://blog.crossjoin.co.uk/2019/04/25/skip-test-connection-power-bi-refresh-failures/

Web.Contents(), M Functions And Dataset Refresh Errors In Power BI

Privacy and Credentials

Power Query Security: Evidence Left Behind
The CredentialQuery Option For Web.Contents() In Power BI Custom Data Connectors

Binary data

Parsing Binary data using Power Query formulas
Read headers of.png images

Using Python

More

https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

Learning resources
Handling BLANK in Power BI
Safe click trick in Power BI

https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

Bookmarks in Power BI – What this Data mean?
Dynamic filtering in Power BI

Tooltips

Enhance Power BI report with Tooltip pages