Translate M code to SQL code (and vice versa)

Power Query M Code Showcase
1

Hello,

As a Power BI developer you don’t always create stuff from scratch. At the moment I need to deal with the work of previous developers as well: the good, the bad and the ugly…
ie. I am working on a report that has lots of Power Query steps, has a data model that isn’t necessarily looking like a star, has a lot of calculated columns, etc. The developers who built it are not available anymore. There is too much already built to throw it all away and start from scratch…

Generic question: what are your best practices in a situation like this?

More specifically:
In general I believe in doing transformations as close to the source. Unfortunately I can’t create or get specific database views, but for some tables I have translated the PQ steps to SQL code (which is basically my only/‘source step’ in Power query). Is that something you would recommend?

Is there a place on the internet with (the most common) transformations in M code on one side and SQL code on the other side?

Any advice for bringing the Calculated columns in DAX back to Power Query or even SQL?
(these seem a bit trickier, because of context etc). What are your experiences?

I would appreciate some advice, pointers or good blogs/websites or YT videos.

Thanks in advance

KR
J

1 Like
2

Hi @Johnny, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

3

Hi EnterpriseDNA,

Unfortunately I cannot share the PBIX file due to sensitive data. It is also not that I’m requesting someone here to fix the issues that are there with the model.
I am more interested in the link between M code, DAX code and SQL.
Maybe this is not the right place to ask for it, but I am wondering what’s the best practice in moving existing M and/or DAX to SQL code. I expect that pushing things closer to the sources will lead to significant performance improvements.

Maybe my question is too broad as I’m more looking for some good blogs or videos about this topic than a specific code that I want to move from a to b?

Thanks!

1 Like
4

Roche’s Maxim of Data Transformation is

“Data should be transformed as far upstream as possible, and as far downstream as necessary.”

Roche’s Maxim of Data Transformation – BI Polar (ssbipolar.com)

Query Folding will also be your friend

Query folding is the ability for a Power Query query to generate a single query statement to retrieve and transform source data

In other words, you peform the Power Query steps in a way in which it creates a SELECT statement to your source SQL data which is very performant.

Query folding - Power Query | Microsoft Docs

With regards to your calculated columns, I’d imagine most of the calculated columns could be converted into steps in Power Query. Just depends on what they are.

There is lots of great content in the Enterprise DNA learning portal on M/Power Query/Data Transformations.

Guy in a Cube on Query Folding on YouTube, I can’t get to the link just now as I’m on my work laptop.

Hope this helps.

5

Hi @Johnny

This issue was once addressed here: Solved: M query to SQL - Microsoft Power BI Community

You can check this out and I hope it proffers a solution to your question.

Thanks,
Mukaila Adesina

1 Like
6

Hello @Johnny, it’s been a while since we got a response from you. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

7

Hi @Johnny, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.