If you’re interested in cascading parameters and Power BI custom connectors is an option for your project, you should give a try to Navigation Tables. Which soon enough could make their way into M standard library. If this is not an option, you might find your solution in the next few scrips tangled between functional programming concepts. You will also learn about:

  1. Where to set the allowed values for function parameters
  2. How to build a function inception with higher order functions
  3. How to access a variable outside its scope
  4. And finally get to a result which mimics cascading selections

While I enjoy that now I can write PowerQuery expressions everywhere in Analysis Services, the fact that I don’t know what is available at hand when I embed an M script in my model — is a bit of an annoyance. Especially when that script ends up working in Data Tools and Power BI but spits up various errors during processing of a table.

Expression in partition ‘Partition’ in table ‘SharedExpressionTable’ references an unknown entity.

Being able to access the #shared variable would solve the issue. However the only way to access it is by creating a cube level expression…

Recently I started exploring the limits of standard charts in Power BI and ended up drawing all sorts of mathematical functions. For the fun of it, I’ve decided to port a famous Homer Simpson-like curve to DAX and was really impressed by the result.

As always, there is no magic involved, just the usual trio:

  1. Prepare some iterator ranges in M
  2. Add some curve functions in DAX
  3. Visualize it 😍

Depending on how you look at this, the final solution can be summarize into 65 LOC = 15 LOC of M + 2*25 LOC of DAX. …

Whenever I’m faced with a data mashup problem in Power BI, I try to check if it can be resolved with a standard M functions exposed by the intrinsic variable . Navigating this data structure when you don’t know what you’re looking for seems to be tedious task. Up until recently my function discovery workflow included to turn the output of #shared into a table and then search for keywords in the name of the functions. Hoping that the developers called the functions accordingly. …

Power Query is a great tool for data mashup, however when it comes to really show its powers as a desktop ETL tool, you realise that no matter how powerful at E an T it is, the L is falling behind, as you can only load the data into the embedded tabular data model that comes with Power BI.

Ignoring R, local cubes and HTTP POST requests, I wanted to showcase a few other methods for exporting data from Power Query and loading it to SQL Server.

The logic I follow is very simple — because there is no obvious…

Regardless of what are you requirements, be that providing some user context during slicing of data or a filtering mechanism for data querying, having a function like USERNAME() in Power Query toolbox can greatly simplify your data mashup scripts. While trying to build a more robust approach for self referencing queries in M, I’ve managed to collect 4 methods on getting the current username in Power BI. These methods rely on a variety of dependencies — R, NTFS, LocalDB and PowerShell, and come with their own advantages and disadvantages. …

TL;DR Another place to find your Power BI queries is in the data sources of the embedded tabular model. They are stored in a zip file which is encoded as a base64 string in the Mashup field of the connection string.

While we are waiting for Microsoft team to release new features that would allow exporting Power Query queries outside Power BI ecosystem, we might as well explore all of the other possibilities available to us. So far I’ve counted 3 ways of getting a hold of our precious M queries:

  1. Sending a frown email
  2. Parsing the *.pbix files
  3. Using…

The quest to fetch the details of the ever evasive $Embedded$ connection to the local tabular data model has always interested the Power BI community. There are numerous tips on how to obtain the details involving either shell or PowerShell scripts or even V..B..A…

I want to share with you an approach that is based on using R.Execute() in Power Query, and has the advantage of not implying a dependency outside the Power BI environment. …

I rarely use in Power BI, but when I do, I make sure to keep the data mashup out of it. I don’t have anything against R, it’s arguably the best statistical tool out there, however when it comes to using an expressive language for doing data wrangling, Power Query (M) is my language of choice.

Although R has been introduced to Power BI ecosystem as a complimentary tool, its capabilities exceed by far those of the Microsoft’s analytics suite. …

Although matrices are not first class citizens in Power Query, quite often a business problem comes along which might greatly be simplified by thinking of it in matrix terms.

You can of course imitate matrices in Power BI by means of tables, two dimensional lists or records but this approach doesn’t come with any matrix related functions. So as always you have to roll up your sleeves and roll out your own set of needed operations.

I would like to share with you an approach of doing matrix multiplication which can be translated into other algebra operations.

We’ll start by…

Igor Cotruta

Business intelligence developer interested in shiny charts and dim lit data models

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store