Power Query: Internal web report view - date parameter

[excelaudio]

I need to try and set-up PowerQuery to an internal web report viewer. The report viewer requires dates to be selected before showing any results. I am trying to avoid back-end access via SQL, so I was wondering is there is a way to pull the necessary data with specific date parameters?

 

[ImkeF]

This depends on what kind of web Report Viewer you're using/how Power Query accesses it.

Advantage of SQL-access actually is that it accepts parameters to be passed in as filters who filter at the source.

So if the filters of your web-report-Service are part of the URL, this normally shouldn't be a problem: You create a concatenated field in Excel or via Power Query that combines the fixed and variable part respectively (using "&" as the concatenator).

If there are multiple pages to pull, this is a good example of doing it efficiently: Iterating over multiple pages of web data using Power Query | Matt Masson (The list of 1..7 would probably need to be replaced by an Excel-table of your different filter-criteria)

 

[excelaudio]

Unfortunately, the date requirement is not part of the URL. I found the link you provided, however, it doesn't help me navigate past this date requirement. It seems the date is some sort of Java field from what I can gather. Regarding the type of site, I'm not 100% sure.

The issue I have with accessing the data via SQL is there is a lot of red tape at our company and it takes an act of Congress to get access to the backend (SQL database). While it is possible, I was hoping this could be avoided. Again, the URL I go to, it has an option to select the dates from the loaded page. There are 2 date sections, a beginning date to select and and end date to select. Once those are selected, I would then click on the report button on the site.

This depends on what kind of web Report Viewer you're using/how Power Query accesses it.

Advantage of SQL-access actually is that it accepts parameters to be passed in as filters who filter at the source.

So if the filters of your web-report-Service are part of the URL, this normally shouldn't be a problem: You create a concatenated field in Excel or via Power Query that combines the fixed and variable part respectively (using "&" as the concatenator).

If there are multiple pages to pull, this is a good example of doing it efficiently: Iterating over multiple pages of web data using Power Query | Matt Masson (The list of 1..7 would probably need to be replaced by an Excel-table of your different filter-criteria)

 

[excelaudio]

Has anyone else got any ideas? If it can't be done, then can someone tell me it can't be done?

Thanks,