SQL query to PowerQuery M language/code

[praveenlc]

Hello! I am looking to achieve similar SQL query in PowerQuery M language/code

I know its simple in DAX using 'CALCULATE' function but all the data needs to be imported to report

I am dealing with the huge amount of 'Factless Fact Table data' and can't really efford to load everything to the report which is exceeding the PowerBI PRO size limits

Tried DirectQuery but facing performance issues (my source is Azure MSSQL DB General Purpose: Gen5, 2 vCores). Any suggestions on performance tuning is also welcome

But I really need help on PowerQuery, appreciate in advance

EXMAPLE QUERY-1
SELECT
COUNT( DISTINCT Id ) AS Assigned,
COUNT( DISTINCT Id + IIF( Response=1, '1', null )) AS Processed,
COUNT( DISTINCT Id + IIF( Response=0 AND IsDuplicate=1, '1', null )) AS Duplicate
FROM TABLE WITH (NOLOCK)
WHERE Category='xyz' AND CustomerId=123 AND Type='I' AND Status IN (1,2,3) AND [Date] BETWEEN '2021-06-01' and '2021-06-02'

EXMAPLE QUERY-2
SELECT
COUNT( DISTINCT Id ) AS Assigned,
COUNT( DISTINCT CASE WHEN Response=1 THEN Id ELSE NULL END) AS Processed,
COUNT( DISTINCT CASE WHEN Response=0 AND IsDuplicate=1 THEN Id ELSE NULL END) AS Duplicate
FROM TABLE WITH (NOLOCK)
WHERE Category='xyz' AND CustomerId=123 AND Type='I' AND Status IN (1,2,3) AND [Date] BETWEEN '2021-06-01' and '2021-06-02'

Lastly, I have tried most of the Azure performance techniques and PowerQuery community for M code but am unlucky.

[parry2k]

@praveenlc I really don't know what you are trying to achieve and why PQ? Didn't @selimovd  reply solved that you run these queries directly with fixed value? Am I missing something in your question?

[parry2k]

@praveenlc or you can explore dynamic PQ Parameters that way you don't have to live with the fixed value data.

Dynamic M query parameters in Power BI Desktop (preview) - Power BI | Microsoft Docs

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[praveenlc]

Thanks @parry2k , its a good option totally fits my situation and I am exploring it.

And I am still crazy to achieve this in PowerQuery M code for my knowledge, any help here? 🙂

[selimovd]

@praveenlc you can also just select your table without SQL statement and try to do the transformations in PowerQuery if you like the challenge

[selimovd]

Hey @praveenlc ,

why don't you use the SQL statement in DirectQuery mode?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic