Поделиться через

Refresh PowerQuery connections using powerBI data source

  • Статья

First thing first

1) We can refresh power query connections created against SQL Server and Oracle only at this point.

2) PowerQyery connection against SQL or Oracle which has native query are not supported

 

What is native query?

When we create powerquery against SQL Server we will get below something like,

 

 

Here if you select SQL statement it will give an option to enter query, this query will be considered as native query.

 

 

 

 

So no refresh for native query and powerquery connection to sources other than SQL and Oracle. For example Odata feed. (Might be possible in future but not now!!!)

 

So once we have valid SQL PowerQuery connection in excel file next thing will be creating data source from PowerBI admin,

So go to PowerBI admin centre and select data sources -> New data Source -> Power Query

 

 

 

It will ask for connection string,

 

This connection string we can get from Excel file.

In excel file go to Data -> Connections

 

 

Here select your powerquery connection and select Properties.

 

 

 

Go to definition tab,

 

 

Select connection string from here.

 

It will not be very understandable but it works!!!

Sample connection string:

Provider=Microsoft.Mashup.OleDb.1;Data Source=$EmbeddedMashup(817587b2-a68c-4de3-89a4-d870e2ac80e5)$;Location=BinaryTest;Extended Properties="UEsDBBQAAgAIAGaW2kQlNJBXqwAAAPoAAAASABwAQ29uZmlnL1BhY2thZ2UueG1sIKIYACigFAAAAAAAAAAAAAAAAAAAAAAAAAAAAIWPzQqCQBSFX0Vm750fS0qu46JVkBEE0VbGSYd0DB3Td2vRI/UKBWW0a3fOx7c453G7YzLWlXfVbWcaGxMOjHjaqiY3tohJ707+giQSd5k6Z4X2XrLtorHLY1I6d4koHYYBhgCatqCCMU6P6WavSl1n5Cub/7JvbOcyqzSReHiPkQK4gCAMGfCZQDphTI2dMoc5BGIZAkP6g3HVV65vtdTWX2+ + /9xJkL/0TdG1BLAQItABQAAgAIAGaW2kQlNJBXqwAAAAAEwAAAAAAAAAAAAAAAAD3AAAAW0NvbnRlbnRfVHlwZXNdLnhtbFBLAQItABQAAgAIAGqW2kTpZ0KShwAAALwAAAATAAAAAAAAAAAAAAAAAOgBAABGb3JtdWxhcy9TZWN0aW9uMS5tUEsFBgAAAAADAAMAwgAAALwCAAAAAA=="

I have removed few of the characters from Extended Properties so it will not work if you try!!! :P

Next thing will be add this connection string in connection string text box of Power BI admin centre data source and click on next,

Next screen will look like,

 

Here initially you will not get status as “Configured”, you have to add correct Gateway and credentials to get it configured.

 

Then test power query connection.

 

 

 

If it shows success, you are ready to use this connection to refresh data in your Excel sheet.

 

 

 

 

 

 

That’s all from me…

 

If you have any questions feel to add in comment.

Comments

  • Anonymous
    December 23, 2014
    Hi you seem to have this working... I am confused on what password is the power query datasource asking for when configuring You article does not have this

  • Anonymous
    December 23, 2014
    Hi marc, I hope you are talking about one in Power BI admin center on portal. that would be password for DB user and if it is SQL server and you are using windows login then password for that windows user. in case I'm wrong could you guive details of where exactly it is asking for password and you are trying to connect to what through powerquery? Thanks, Dilkush