Tecflix

Advanced Data Loading Purchase the entire course

21 December 2018 · · 245 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 to play the video.

  • Power BI New Features and Course Updates 1-min Free—Watch Now

  • What is Power BI? 59-min Watch with Free Subscription

  • Licensing 8-min

  • Connecting to Data Sources 42-min

  • Connecting to Data Sources (2018 Updates) 23-min

  • Data Import Concepts 55-min

  • Transforming Data with the Query Editor 1-hour 20-min

  • Transforming Data with the Query Editor (2018 Updates) 13-min

  • Advanced Data Loading 49-min

  • Modelling Data 1-hour 10-min

  • Modelling Data (2018 Updates) 14-min

  • DAX and the Data Model 31-min

  • DAX and the Data Model (2018 Updates) 10-min

  • Power BI Desktop vs Excel 37-min

  • Cloud and Desktop Power BI Dashboards and Reports 45-min

  • Power BI Reports (2018 Updates) 40-min

  • Building Reports in Excel 32-min

  • Sharing, Distribution, and Security 42-min

  • Data Refresh 8-min Free—Watch Now

  • Administration and Auditing 11-min Watch with Free Subscription

  • Azure Analysis Services 1-hour 3-min

  • Power BI Premium and Power BI Embedded 36-min Watch with Free Subscription

Purchase a Full Access Subscription

 
Individual Subscription

$480/year

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

from $480/year

For small business & enterprise.
Group Purchase
 
  • You can also redeem a prepaid code.
  • Payments are instant and you will receive a tax invoice straight away.
  • We offer sales quotes/pro-forma invoices, and we accept purchase orders and bank transfers.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.
  • See pricing FAQ for more detail.
In collaboration with
Project Botticelli logo Oxford Computer Training logo SQLBI logo Prodata logo