Forum Discussion

DIACHROMA's avatar
DIACHROMA
Helper II
5 years ago
Solved

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

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 sourc...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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's avatar
mahoneypat
Microsoft Employee
5 years ago

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's avatar
DIACHROMA
Helper II
5 years ago

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