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.