So we have a series of reports on our company system that are accessed through links in the UI. When you are in the UI, you simply navigate to the page you want and then click on the link and the system downloads the report into your downloads folder. The URLs look like this:
We used to use VBA to get refreshed reports from within an Excel sheet, but when I try to use the GetData From Web, the resulting table is just an HTML table that says my login has expired.
The query seems to be opening the URL correctly, but it jumps out of the window to give me the download dialog where I can save or open the file just as if i was using the UI, but I can figure out out how to get Power Query to open the file IN Power Query and let me do my transforms on it from there. Any suggestions?
Here is what it looks like when I go through the Get Data from Web: see attachment
And here is the M code that the query generates:
let
Source = Web.Page(Web.Contents("https://XXX.XXX.com/admin/error-refreshing-connections-dashboard-report/report/report_name.html?quarterId=589&stateId=8&name=state%20qtr%20type%20by%20District%20Report%2010-19-2020")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}})
in
#"Changed Type"
Hi Ian,
The 'from web' connector gets data from a web page. It can't download the file for you and then open it. Are you able to link directly to the file download link instead?
Mynda
Hi Mynda. What do you mean by linking to it directly?
The Excel file itself should have a URL to download it. The first page is just a gateway to the real URL for the file.
Yes, I can put the URL in a cell, and when i click on it it opens the file in a new workbook. is there a way that power query can fetch the data and drop it into the current workbook and then refresh it periodically?
Have you tried putting that URL into Power Query get data > from web?
