Pulling in variable to Power Query

[mattadams84]

I am trying to pull in a variable from an excel sheet to use in power query.

I am basically calling an API and use the following code (this works perfectly by the way)

let
    pagination = (Page as number)=>
let
    Source = Json.Document(Web.Contents("https://api.my-data-api.com/todays-data?key=xxxxxxxx&date=" & Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), 10), "YYYY-MM-DD")&"&page="&Number.ToText(Page))),
    data = Source[data]
in
    data
in
    pagination

What i want to be able to do is change the numerical value of

Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), 10),

So in this case i want to change the number 10 and to get that number from a sheet in my workbook.

I have created a table on a worksheet and called the table GET_DATA. This is a one row table with the number contained in a column called "ID"

How can i change this number "10" currently hardcoded to be a variable?

 

[Shortmeister1]

Very difficult to find this online or in any book.

You can either use parameters inside Power Query or, as I prefer to do, set up a single cell table on an Excel worksheet.

1. Name the table - eg. "myParameter"
2. Now jump to the PQ Editor, create a blank query and type in the following steps:

a) Step1: =Excel.CurrentWorkbook(){[Name="myParameter"]}[Content]
b) Step2: = Source{0}[myParameter]

3. Once you've done this, you can reference the parameter in your main query. Just replace 10 with your parameter name.
eg. Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), myParameter),

 

[mattadams84]

Many thanks for the reply.

I currently have the number I want to collect in a table called GET_DATA. It's a two column table with headers and the number I want is in a column named ID. Would I be able to get it from there or does it necessarily have to be a one cell table (without headers)

 

[Shortmeister1]

I've not tried it, but 0 is the position in the table. I suspect that you can use a two dimensional reference with a comma but I haven't tried it. Do remember that Power Query is base 0 not base 1.

 

[Shortmeister1]

Hi

Now I'm awake and at my PC, I checked out a few steps for you with a 5X2 table called "myParameter"

So the references would be = Source{0}[ID]

= Source{0}[ID]	= Source{0}[Value]
= Source{1}[ID]	= Source{1}[Value]
= Source{2}[ID]	= Source{2}[Value]
= Source{3}[ID]	= Source{3}[Value]
= Source{4}[ID]	= Source{4}[Value]

 

[mattadams84]

Ok, great, that does work, it pulls the number in to Power Query successfully. The problem i have is that it seems to break fetching the data from the API. I either get an error saying that failed to expand column 1 or:

Formula.Firewall: Query 'ChooseMatchesAPI' (step 'pagination') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Here are the three queries i use to fetch the data:

This is the query called GET_DATA (the one that you created)

let
    Source = Excel.CurrentWorkbook(){[Name="GET_DATA"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"GET DATA FROM", type datetime}, {"DAYS FROM NOW", Int64.Type}}),
    #"Changed Type1" = Source{0}[DAYS FROM NOW]
in
    #"Changed Type1"

This is the query that deals with pagination of the API, called ChooseMatchesAPI

let
    pagination = (Page as number)=>
let
    Source = Json.Document(Web.Contents("https://api.football-data-api.com/todays-matches?key=xxxxxxxx&date=" & Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), GET_DATA), "YYYY-MM-DD")&"&page="&Number.ToText(Page))),
    data = Source[data]
in
    data
in
    pagination

And this is the main query that outputs the data called ChooseMatchesAPIPages:

let
    Source = List.Generate(()=>
        [Result= try ChooseMatchesAPI(1) otherwise null, Page=1],
         each [Result]<>null,
    each [Result= try ChooseMatchesAPI([Page]+1) otherwise null, Page=[Page]+1],
    each [Result]),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"homeID", "awayID", "odds_ft_1", "odds_ft_x", "odds_ft_2", "odds_ft_over05", "odds_ft_over15", "odds_ft_over25", "odds_ft_over35", "odds_ft_over45", "odds_btts_yes", "odds_1st_half_result_1", "odds_1st_half_result_x", "odds_1st_half_result_2", "odds_1st_half_over05", "odds_1st_half_over15", "date_unix", "home_name", "away_name", "competition_id"}, {"homeID", "awayID", "odds_ft_1", "odds_ft_x", "odds_ft_2", "odds_ft_over05", "odds_ft_over15", "odds_ft_over25", "odds_ft_over35", "odds_ft_over45", "odds_btts_yes", "odds_1st_half_result_1", "odds_1st_half_result_x", "odds_1st_half_result_2", "odds_1st_half_over05", "odds_1st_half_over15", "date_unix", "home_name", "away_name", "competition_id"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Expanded Column2",{"homeID", "awayID", "date_unix", "odds_ft_1", "odds_ft_x", "odds_ft_2", "odds_ft_over05", "odds_ft_over15", "odds_ft_over25", "odds_ft_over35", "odds_ft_over45", "odds_btts_yes", "odds_1st_half_result_1", "odds_1st_half_result_x", "odds_1st_half_result_2", "odds_1st_half_over05", "odds_1st_half_over15", "home_name", "away_name", "competition_id"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Reordered Columns1",{"competition_id", "homeID", "awayID", "date_unix", "home_name", "away_name", "odds_ft_1", "odds_ft_x", "odds_ft_2", "odds_ft_over05", "odds_ft_over15", "odds_ft_over25", "odds_ft_over35", "odds_ft_over45", "odds_btts_yes", "odds_1st_half_result_1", "odds_1st_half_result_x", "odds_1st_half_result_2", "odds_1st_half_over05", "odds_1st_half_over15"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"competition_id", "COMP_ID"}, {"homeID", "HOME_ID"}, {"awayID", "AWAY_ID"}, {"date_unix", "DATE_UNIX"}, {"home_name", "HOME_TEAM"}, {"away_name", "AWAY_TEAM"}, {"odds_ft_1", "ODDS_FT_H"}, {"odds_ft_x", "ODDS_FT_X"}, {"odds_ft_2", "ODDS_FT_A"}, {"odds_ft_over05", "ODDS_FT_O0.5"}, {"odds_ft_over15", "ODDS_FT_O1.5"}, {"odds_ft_over25", "ODDS_FT_O2.5"}, {"odds_ft_over35", "ODDS_FT_O3.5"}, {"odds_ft_over45", "ODDS_FT_O4.5"}, {"odds_btts_yes", "ODDS_FT_BTTS_YES"}, {"odds_1st_half_result_1", "ODDS_HT_H"}, {"odds_1st_half_result_x", "ODDS_HT_X"}, {"odds_1st_half_result_2", "ODDS_HT_A"}, {"odds_1st_half_over05", "ODDS_HT_O0.5"}, {"odds_1st_half_over15", "ODDS_HT_O1.5"}}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Renamed Columns",{"HOME_ID", "AWAY_ID", "COMP_ID", "DATE_UNIX", "HOME_TEAM", "AWAY_TEAM", "ODDS_FT_H", "ODDS_FT_X", "ODDS_FT_A", "ODDS_FT_O0.5", "ODDS_FT_O1.5", "ODDS_FT_O2.5", "ODDS_FT_O3.5", "ODDS_FT_O4.5", "ODDS_FT_BTTS_YES", "ODDS_HT_H", "ODDS_HT_X", "ODDS_HT_A", "ODDS_HT_O0.5", "ODDS_HT_O1.5"})
in
    #"Reordered Columns2"

If i hardcode the number in the pagination API, then it works fine, if i use GET_DATA instead of the hardcoded number i get errors.

I unfortnately do not know much about Power Query, i built this basically by watching youtube tutorials, but it works so thats at least i start. I just want to change dynamically the day i am fetching data from.

Any help much appreciated !

 

[Shortmeister1]

Can you check that the number is actually a number and not text masquerading as a number. You might need to insert an extra step to change the type.

If that doesn't work, can you post the errors.

 

[mattadams84]

I think it is a number, in any case I have a 123 symbol for the query and in the advanced editor i see it has an int64.type.

This the error i get :

Formula.Firewall: Query 'ChooseMatchesAPI' (step 'pagination') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

 

[Shortmeister1]

I believe that it's a firewall error.

I've just had a look at

Behind the scenes of the Data Privacy Firewall - Power Query

Describes the purpose of the Data Privacy Firewall

docs.microsoft.com

In short, you can modify your privacy settings - but understand the implications - or it seems to suggest that you can combine the queries and pit the table query as a distinct step or two at the beginning of the main query. You'll need to use the advanced editor to do this. Not too complicated but make sure that you back up and the original text and preferably work on a copy!

 

[mattadams84]

OK I see, i will see if i can merge the queries using the advanced editor. If i dont i will change the privacy settings! Many thanks !