Okay, Here it goes!
First, we need to create the cells that will act as parameters
If you see the name of the cell is A2 right now. First, we change the name of the cell to the “Name of the table”
In this case we change it to End Period
Then we click in the From Table/Range button in the Data Ribbon
Then, make sure that the table icon is at the left of the name of the cell
Then click on Save and load, A new sheet will be loaded
Please delete the sheet so that the message reads, connection only.
Repeat as needed
Then, go to New source in the Query Editor > Other Sources > ODataFeed
Then enter your BAQ with some random parameters to establish connection.
https://yourserver/instance/api/v1/BaqSvc/YourBaq?BeginPeriod=7&EndPeriod=7&FiscalYear=2017
Then Select Close and Load
Then open again the query editor, select Query1 or whatever the name of the table that contains the BAQ is.
You will see something like this
Now we will create the functions that will reference the cell value
Then we just create 3 variables and then call our function referencing our Table
Then we reference it in our parameters, if you see after the main request that ends in the BAQ ID we need to type a closing question mark “?” and then we start calling our parameters, that is why I have BeginPeriod=, then we add an “&” to concatenate the text with the value of the variable, in this case we don’t use double quotes since it is a string.
Don’t forget to add an ampersand as part of the string at the beginning of the following parameters.
Then Press Close and Load… and That is it. You can now change the value of the cells, hit refresh and it should be working.
What I did is just cut the cells, added some rows at the top of the query to keep everything in the same worksheet
Hope this helps someone! I searched like crazy a lot of different methods, this is the one that worked for me. Any questions please let me know!