Dynamically create types from text with Type.FromText in Power Query and Power BI

In this article I’ll show you how to create types from text in Power Query, enabling you to dynamically change types via functions for example. It’ll come out as a custom Type.FromText function which has been asked for in the comments of this article: https://www.thebiccountant.com/2017/01/09/dynamic-bulk-type-transformation-in-power-query-power-bi-and-m.

Problem

To transform a column to type text can be done like so:

Table.TransformColumnTypes(Source,{{"Column1", type text}})

This transforms the “Column1” from table “Source” to type text.  Now, if you want to make the type dynamic and move it to a function parameter like so:

(VariableType as type) =>

Table.TransformColumnTypes(Source,{{"Column1", VariableType}})

This returns a function dialogue as follows:

Read more

Performance tip for aggregations after joins in Power Query and Power BI

This article was edited on 2nd Nov 2019. JoinKind.Local has been removed to avoid problems with merges on tables with primary keys:

In this article you’ll learn how to speed up the aggregation of joined/merged tables by orders of magnitude (I recorded up to 30 times faster execution times). This method works for merges where both table have multiple rows for each keys. If one of your tables has a primary key, the method Chris Webb describes here works just as good: Chris Webb’s article on how to improve performance on aggregations after joins using primary keys .

You can follow along the different methods in this file:  PerformanceAggregationsAfterMerges1_Upload.zip

Background

When you join a table to another table in Power Query, the UI gives you the option to either expand the columns (default) or aggregate the contents of the joint tables. That’s useful if multiple rows are returned for the rows of the table that has been joined to (left table):

Read more

Parent-Child Hierarchies with multiple parents in Power BI with Power Query

I’ve written about a method to dynamically flatten parent-child-hierarchies also with multiple parents some while ago here. I’ve actually used this approach for Bill-of-materials cases and refined that approach in a series starting here. There, the quantities are aggregated in M already, as they are not supposed to change. But if one wants to use the hierarchical structure to report on transaction tables where several filters shall be applied, one has to adjust the data model a bit:

DimNodes

If you have parent-child-hierarchy with multiple parents, my function will a table like below, where the children with multiple parents still reside in different rows:

Read more

Power BI administration made easy with Power BI REST API custom connector

Today I read the (as always) great article by Matthew Roche “Governing Power BI just got a little easier” and couldn’t find a description on how to get to this promising window with all the admin goodness. So here it comes how to build your Power BI REST API custom connector then 😉 :

Create a custom connector for the Power BI REST API

Miguel Escobar has done a fantastic job to make it super-easy for you here.

Edit 30th September 2019: This repo has just been updated and includes a version with API secret. So if you’ve downloaded that content before and got an authorization error, please get the new files.

Get data from your connector

After you’ve stored the .mez-file in the correct folder (C:\Users\<YourUserNameGoesHere>\Documents\Power BI Desktop\Custom Connectors) and open Power BI Desktop again, you will be greeted with this warning message: Read more

Compare Power BI files with Power BI Comparer tool

Have you ever wanted to compare a version of a Power BI file with a previous one? … In the unlikely case that you haven’t yet, just wait until the auditors are in again – my new Power BI Comparer tool will save your day then 😉

Or maybe even before you uploade a new version of a report that has already been published to the service. How do you communicate the changes to your colleagues? Wouldn’t it be nice to have a tool that performs that comparison and documentation fully automagically?

Power BI Comparer

Fortunately my Power BI Comparer-tool makes it super easy to compare all properties of 2 Power BI files with each other: Just convert your pbix-files to pbit (as we need to access the data model properties as well) and drop the paths to these new files in my Excel-file like so:

Read more

Export large amount of data from Power BI desktop visuals

I’m going to show how to export data from visuals in Power BI Desktop that’s too big to be downloaded by the native functionality and therefore returns this error-message:

Export data from visuals

Check if you really need this

Although the method is fairly simple, there are simpler methods if you just need the raw data from your data model (and not the specific aggregations or measures that the visual contains): Read more