Project Botticelli

Advanced Data Loading Purchase this course

21 December 2018 · 69 views

Power BI Course

Advanced Data Loading

Everything that happens in the Query Editor is translated, behind the scenes, into a language called M. M is a functional language designed specifically for data loading and transformation. Generally speaking you will not need to write M code yourself very often because the user interface is so flexible - and this is a good thing because the Advanced Editor for writing M in Power BI Desktop is in fact very basic.

One of the most powerful advanced features available in the user interface is the ability to turn a query, usually one that uses parameters, into a function. Functions allow you to apply the same set of steps to multiple different data sources; they are also key to how Power BI is able to combine data from multiple, identically-structured text files.

Query folding refers to the way the M engine is able to push the logic described in the steps of your query back to certain data sources. For example, when connected to a relational database like SQL Server, query folding involves the M engine translating your steps into a single SQL query that can be run against the database. This can only happen for certain data sources, like relational databases, that have a query language that the engine can generate and is very important for getting good performance when you are working with large amounts of data.

In some cases, for query folding to take place, data must be sent from one data source to another - which could present a data privacy risk. This is handled in the Query Editor through data privacy levels associated with data sources. The rules are that data from a source marked with the privacy level ‘public’ may be sent to any other data source; data from a source marked as ‘organizational’ may only be sent to another source marked as ‘organizational’; and data from a source marked as ‘private’ may never be sent to another data source. Depending on the data privacy levels set, query folding may or may not take place and indeed a query may or may not run successfully. You can instruct Power BI to ignore data privacy levels when combining data from different sources but this is not recommended.

Log in or purchase access below to the premium version of this content.

Purchase This Course or Full Access Subscription

Single Course

$250/once

Access this course for its lifetime*.
Purchase
Subscription Best Value

$480/year

Access all content on this site for 1 year.
Purchase
Group Purchase

from $480/year

For small business & enterprise.
Group Purchase

  • Redeem a prepaid code
  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a Course Retiring.

Online Courses