I'm pulling raw data from a web report every month which I then use to calculate quartile averages. I have to select the beginning month and ending month from the report filters before I run it. I've set up a power query to pull the raw data for me. I'm trying to set up a parameter that would reference to a cell so that I can enter the month in a particular cell and the query would reference to it.
The URL is pretty lengthy but the part that pertains to the month selections is below:
....yr=2016&u_min_mth=6&u_max_mth=6....
"u_min_mth" is the beginning month and "u_max_mth" is the ending.
I under that I need to set up a parameter table. How do I get the table to refer to this specific part of the URL?
Any help would be appreciated.
Hi Kumail,
In session 7.02 of the Power Query course I show you a technique for that.
Please let me know if you get stuck.
Mynda
Hi Mynda,
I was able to access the lesson pertaining to parameters and now I'm trying to set up a parameter using the function technique that you have described in session 7.02
Since my variable is the month number, I have the following code in my query editor (I've displayed only part of the URL since it is very lengthy):
let
fnMonth = (BegMth as number) =>
let
Source = Web.Page(Web.Contents("...........subfcc=05.09.0&u_yr=2016&u_min_mth="&BegMth&"&u_max_mth=6&u_mgr=".........)),
Data2 = Source{2}[Data]
in
Data2,
#"Invoked FunctionfnMonth" = fnMonth(1)
in
#"Invoked FunctionfnMonth"
When I invoke it I get the following error:
"Expression.Error: We cannot apply operator & to types Text and Number"
I'm not sure why the ampersand sign is causing the error.
Not to get ahead of myself, but I also need to setup a parameter for the ending month. Should I set up both functions for beginning and ending month at the same time?
Thanks,
Kumail
Hi Kumail,
When you join data together you can only join text to text, and the end result will also be text. You have to convert your month numbers to text. You can do this in Excel using the TEXT function, which is probably easiest in this scenario. Or you can convert it in Power Query using the Text.From function.
Mynda
