Power Query Editor not available for certain data connections

[jmerritt]

I have an excel file that users in a different department will be maintaining. Normally, I have built dashboards from files saved locally on my machine and prepped the data using the power query editor and/or DAX functions. Since we want to allow other users to modify the file, we want to store it on the cloud somewhere. I've tried connecting to a file in Sharepoint and also a power bi service dataset but the "power query editor" isnt available when I do this (I can't perform any data prep / manipulation). Are there only specific cloud connections available where I can use the power query editor to transform data in an excel file? If so, which methods would you recommend?

[jmerritt]

@v-angzheng-msft 

Thanks for your response! After looking more into this, the easiest way for our organization is to share files through Box.com. I don't see any dedicated connector for this and I've tried to connect using the "web" connector as shown below. Any file on our company's Box domain requires us to authenticate with a user name and password. It looks like the web connector doesnt  support this as it stated "web.browsercontent currently supports only anonymous credentials".

Are you aware of any other way to use an excel file from a shared Box.com link as the data source in Power BI?

[v-angzheng-msft]

Hi, @jmerritt 

If the connection mode of the data source is a live connection or direct query, then PowerQuery will be limited in transforming the data, regardless of some data connections.
You can see how the data is stored at the bottom of the PowerBI report. If not import mode, consider converting connection mode to import mode. Then the Transform Data option on the navigation bar will be available.
e.g.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.