Create table from Direct Query PBI datasource

[misc981]

I have a PBI report that pulls data from multiple PBI datasources.  Have since discovered that it is not possible to transform data from these datasources in Power Query.

I am contemplating creating a table in the PBI report, with values from the PBI datasources, so I can manipulate this table instead.  However, have not been able to identify any method to perform this.

I have access to the datasources utilized by the PBI datasources, but importing is out of the question.  The reason I'm using those PBI reports as datasource in the first place was because there was extensive calculation associated with each individual report, causing severe performance issues.

I just need to work with the summarized result of each report, and not the extensive calculation behind each report.

Can someone advise if it's possible to create table with data from existing PBI reports, and how this can be accomplished?  Is there an alternative way to achieve my requirements, if this is not possible?

[v-achippa]

Hi @misc981,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Shahid12523 for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @misc981,

We wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @misc981,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[Shahid12523 · Accepted solution]

You can’t transform or create new tables in Power Query when using PBI datasets as DirectQuery sources.

Options:

Use composite models → connect multiple PBI datasets, create relationships & measures.

Create calculated tables with DAX (limited).

Push summarization upstream in the source dataset.

You cannot materialize/transform tables inside PQ with DirectQuery datasets.

Best practice: Pre-aggregate in the source dataset, then consume in your report.