Synapse view not showing the data

[Markusb]

I have create a Synapse view on a serverless pool which queries ADLS.

I created a Power BI datasource usig the Azure Synapse Analytics connector with import mode. I can connect and can see my view.

Now when I first created this data source There was an error because one of the files in ADLS was missing and I got an error 
"Datasource error Content of directory https://mydatalake folder cannot be listed.

Could not use view or funcion dbo.myView because of binding error."
At that moment in time that was correct and expected.
I now loaded the file into the ADLS again and the view works in Synapse, but in Power BI Desktop I keep getting the same error. Clicking on refresh doesn't change this.
I when tried removing the data source and re-creating it but no difference.
I even uninstalled PBI Desktop and did a new installation, but I still keep getting this error.

What can I do to get this view working in PBI Desktop?

Any help is appreciated.

[Markusb · Accepted solution]

Ok, I think I've soved it.

The problem seems to be that the SQL login doesn't have permissions on the datalake. I now changed it to my AAD account which does have permissions on ADLS in the data source and can now view the data in PBI.

[rpatil]

How did you changed from sql login to aad account?

[Markusb]

@rpatil Just change the authentication in our Power Bi datasource to organizational. Remember in my case the view in Synapse was working, only when accessing the view from Power Bi it did not work.

[rpatil]

hi @Markusb , thank you for reply. It was already in organizational account. Do you think i am missing some rights? My case is similar to yours in synapse view is working but when i load it it power bi it is stuck on loading screen

[Markusb]

Hmm, not sure. In mycase I got that error that the content couldn't be listed which means either ther folder or file does not exist or you don't have permissions. If it's just stuck on the loading screen I'm not sure. It may also depend on how your external datasource in Synapse is configured. What kind of credential are you using?

[rpatil]

do you add external datasource in openrowset? I tried adding external datasource the qury shows that it ran perfectly when i click on message tab there is thsi error . I am getting the result in synapse even without using externaldata source

[Markusb]

You don't have to use a external data source, but I did and I think it makes it easier if you want to re-use the same storage account in the future.

The error indicates that there is no data in your location.

I really can't tell what is going wrong, if the location is exactly the same as you used before and the credentials for the datasource are correct it should work.

[Markusb]

Just wanted to add two things. My datasource to Synapse is using SQL authentication.

I created a new view (with the same content) and still get this error. Could it be that the SQL authentication is the issue?