How to retrieve Power Query transformations in a single dataset or connect to multiple datasets ?

[DIACHROMA]

Hello everyone, 

I'd like to know if there is a way to create a Power BI report that connects to multiple datasets?

In a nutshell, I perform a very large number of transformations on my sources in Power Query, so it quickly becomes unmanageable and very, very slow with a lot of update issues.

So I'm exploring the possibility of creating multiple datasets rather than a single big dataset that contains all my transformations.

The problem is that for my reports, I need to cross all the tables, I have to find a way to then merge these datasets or to be able to connect to several of them.

Here are the ideas I had, but I don't know if it's feasible:
- Find a way to connect to multiple Power BI datasets
- I am not familiar with Dataflow, could a feature meet my need?
- Perform my transformations in several Excel files with Power Query and retrieve all the tables already transformed in Power BI Desktop (in this case is it possible to configure an automatic refresh of Excel files on Sharepoint?)

If you have any other ideas or examples that work, I'll take it! 😊

For those who wish to have more details, here is why I am confronted with this questioning:

For one of my clients, I work with many Marketing data sources, which are .csv or .xls exports that come from multiple systems or from marketing research offices. These are denormalized tables in Excel format, stored on Sharepoint. Updating is done by storing updated files received by email and automatically stored on Sharepoint using Power Automate.

In order to be able to analyze all this data together, I created a single dataset in which I built a star model. I isolated the dimension information in several tables: DimDate, DimProduit, DimRegion and DimDevise. These tables are linked to several FactTables.

To build my dimension tables, I have to fetch the information in all the sources of the Excel files stored on Sharepoint.

Example: For the DimProduct, I get all the product references, the names of the products and all kinds of product attributes from the different sources. Then, with the rules established with my client, I will standardize the data between them and determine "Product Category", "Product Sub-Category" etc ...

So I have a very large number of transformation steps in Power Query to build my product table!

To relieve my dataset, I was therefore thinking of extracting all the Power Query transformations in a different Power BI file, then integrating the “DimProduct” table already transformed into my dataset.

But I have two constraints:

1. The DimProduct table must be updated in the same way as my FactTables because in each new export we have new products.
2. It must be completely transparent to the customer who wishes to avoid manual tasks. So not possible to copy and paste my DimProduct table from Power BI to an Excel file that I will then integrate into the dataset.

If you have any solutions that I haven't thought of, please share them!

Thank you in advance for your help!
Pauline

[v-yingjl · Accepted solution]

Hi @DIACHROMA ,

" Find a way to connect to multiple Power BI datasets"

- It is not supported in power bi desktop because connecting to power bi datasets uses Live Connection mode which only allows single dataset connection.

"I am not familiar with Dataflow, could a feature meet my need?"

- Dataflow may achieve this that it can create multiple entities under one dataflow, maybe you can refer: Creating a dataflow 

"Perform my transformations in several Excel files with Power Query and retrieve all the tables already transformed in Power BI Desktop (in this case is it possible to configure an automatic refresh of Excel files on Sharepoint?)"

-You can use the folder connector to connect your several files and transfrom them in power query, if the files are in SharePoint, you can connect to SharePoint Folder, refer:

1. Combine And Transform Data Of Multiple Files Located In A Folder In Power BI 
2. SharePoint Folder 

-In this case, if you want to refresh these files in power bi desktop, just click the Refresh button to get the latest data from files; if you want to configure autorefresh in power bi service, you can set schedule refresh for it which will refresh at some time you set automatically, please refer: Configure scheduled refresh 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Maybe your queries can't be optimized further.  I'd have to take a look at them to confirm (e.g., merge steps that may benefit from Table.Buffer, steps that could be consolidated, etc.).  In any case, hopefully the use of dataflows/datasets will solve the issue.

Regards,

Pat

[mahoneypat]

There are likely ways to optimize your existing queries, but using Dataflows and/or Datasets is a good idea to separate and do transformatons offline.  Datasets only allow a Live connection (no additional data transformation), but we expect the release of the composite model feature in Power BI Desktop next week (fingers crossed), which should really help your scenario.

Regards,

Pat

[DIACHROMA]

Hi Pat,

Thank you for your answer !

Regarding the optimization in Power Query, I reduced the volume of data as much as possible (by removing unnecessary columns etc ...). But concerning the steps of transformation, it seems to me difficult to reduce them because I work with very disparate files which I have to make exploitable between them.
But if you have readings on this subject (articles, blog, etc.) I'm interested 🙂

And for the composite data model that's great news! I was looking forward to it but didn't know it was scheduled for next week. My fingers crossed too!

Thanks again,
Pauline