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/measure in a Pivot Table connected to the dataset:

 

after changing the Command text to evaluate 'table' the table refreshes with the full table from the model, which is amazing.

 

I am looking for a way how to load this table using Power Query (in Excel), to do some more data transformation before loading to worksheet.

Can you help me with this? I believe it should be possible to get a single table from dataset using Power Query, when it is possible via legacy connections.

 

maybe via ?

 

 

Thank you

Jakub

 

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi jdusek92 

     

    Yes, you can, connect in Excel or PBI, AS connector, using the navigation steps will have Cube.Transform, or you can put your DAX query in, not just Evaluate a table, more sophisticated one is fine, too.

    Excel connector is the below one

     

    Server is your workspace XLMA endpoint, you need premium capacity or PPU, database is your dataset

     

    Source =  AnalysisServices.Database(Server, Database, [Query="EVALUATE 'yourTable'", Implementation="2.0"])

     

    • 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.