1

Is it possible to refer from Power Query (M) to DAX calculated table? I would like to get DAX table as a source to my power query.

The purpose. I have grouping table made in DAX. I would like to make econometric model with R. So I would like to transform the DAX table with R to get the model parameters. I would like to use these parameters further in DAX measures (not just display them).

Currently I dump the DAX grouping table to Excel file and then pull it up with Power Query.

Improve this question

3 Answers 3

Reset to default
2

Actually, there is a way.

DISCLAIMER: This is a hack. You should not rely on this way.

1. Create DAX calculated table

Input any DAX formula that evaluates to a table in Modeling > New Table.

DAX Calculated Table

2. Check port number using DAX Studio

Connect to your PBI Desktop data model using DAX Studio, and check the port number where the data model is hosted. It should be displayed in the right bottom of the window.

DAX Studio

3. Import the table to Power Query

Click Get Data > Analysis Services and input the address (in my example "localhost:50293") to Server. Then navigate to your DAX calculated table.

Power Query

Improve this answer
4
  • 1
    This is a pirate boarding! Romantic, disrespectful plundering of DAX chests.
    – Przemyslaw Remin
    Commented Feb 4, 2020 at 7:47
  • 1
    NEVER-NEVER do this in your job, please ;-)
    – Kosuke Sakai
    Commented Feb 5, 2020 at 15:31
  • Thanks for warnings. I will take precautions. Do you think there might be any other risk than just not loading the table to powerquery? I just love it. I will test it thoroughly. Have you ever run into any problems with this workaround?
    – Przemyslaw Remin
    Commented Feb 5, 2020 at 17:10
  • The problem you will face promptly is that the port number changes every time you start PBI session. In addition, it may cause inconsistency in the data model. When calculated table is imported via Power Query, it triggers the calculated table to recalculate. So the imported table is referring to a shadow in the past (you will notice that if you include a non-deterministic function, such as RAND or NOW in your DAX table). I'm not sure what risk it potentially has, but after all, there is no reason to take the risk unless you like the thrill.
    – Kosuke Sakai
    Commented Feb 5, 2020 at 17:46
2

it's not possible to refer to a DAX calculated table in M as it's loaded into DAX/Power Pivot engine after M has done the transformations. You can't write to a DAX table after loading into R as well. You can do grouping in M, or if needed run R in the Power Query. One approach that I have used has to load the data, duplicate the query, run a group/filter on the new query, then use that data in a later stage in the report.

Hope that helps

Improve this answer
1
  • In DAX calculated table I use complex measures in grouping. M is obviously not designed for that. So I look for workaround for dumping dax table and loading it to M. Recreating source is not a case here.
    – Przemyslaw Remin
    Commented Feb 3, 2020 at 12:29
1

Jonee is correct. This is not possible. DAX calculated tables are computed after the M queries have loaded and you cannot feed them back into Power Query without saving them externally like you are currently doing.

The M language is more powerful than you might think and very likely could do the same grouping operations, though depending on what they are, it might be fairly difficult. You can also use R or Python script within an M query if you are more comfortable with those.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.