Forum Discussion

jdusek92's avatar
jdusek92
Icon for Advocate III rankAdvocate III
4 years ago

Analyze in Excel Dataset - How to connect to single dataset TABLE using Power Query?

Hello, I am able to load a single Power BI dataset table using Analyze in Excel and changing the MDX query for the  Workbook Connection (not Power Query) that is generated by clicking on a value/mea...
Need Help
jdusek92's avatar
jdusek92
Icon for Advocate III rankAdvocate III
4 years ago

Hello Anonymous 

 

I have only PRO license - but data is loading fine using the legacy workbook OLE DB connection. 

Power Query AnalysisServices.Database returns "The remote server returned an error: (401) Unauthorized."

 

 

 

v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi jdusek92 ,

 

You can open the Power Query editor in Excel and enter the URL of the PPU/Premium workspace. Then you can import the specific tables of the dataset.

 

 

 

 

You need permissions for the underlying dataset if you want to connect to Power BI datasets in Excel. You could have this permission in either of two ways:

  • You could have the Member role in the workspace containing the dataset.
  • You could have Build permission for the dataset in a workspace, or in an app that contains the dataset. Read more about Build permission for Power BI datasets.

 

And please make sure you have enabled XMLA read-write in the Admin portal.

 


If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

  • jdusek92's avatar
    jdusek92
    Icon for Advocate III rankAdvocate III
    4 years ago

    Hello v-kkf-msft  thank you for more info,

    I was able to connect to my PPU trial Workspace using AnalysisServices.Database.

     

    I am still wondering why for Power Query I need Premium Workspace, but the Analyze in Excel connection does not require a Premium. Both connect to the same data source, I suppose?

     

    Also, I prefer to use just the PRO license - is there a way to use any Power Query connector to connect to a PRO dataset/table? Same query as I achieved by editing the legacy OLEDB query in my original post. With this in Power Query I would be able to further transform the table

     

    Warm regard,

    Jakub

    • v-kkf-msft's avatar
      v-kkf-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi jdusek92 ,

       

      When we use AnalysisServices.Database to connect Power BI workspaces, we are actually getting Tabular Object Model from Power BI XMLA Endpoint. XMLA endpoints are supported only for workspaces hosted on Premium capacity. If you will try to connect to a Pro workspace you will get this exception:

      Initial catalog property is required in order to connect to Power BI Pro workspaces.

       

      If you want to connect pro workspace datasets in excel, then you need to use the "From Power BI" function.

       

       

       

      If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
      Best Regards,
      Winniz
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

       

      • jdusek92's avatar
        jdusek92
        Icon for Advocate III rankAdvocate III
        4 years ago

        So it is not possible to recreate a connection to dataset table - that I showed in my original post, using legacy OLE DB - in Power Query?