Forum Discussion
nhaglund
4 years agoNew Member
Pass Variables, and not necessarily Parameters, into an API Call
Hello,
I've issued API calls in the past dynamically using Parameters and is wondering how to do the same with variables in M. My test code is as follows:
let
Today = Date.ToText(Date.From(DateTime.LocalNow()),"yyyy-MM-dd"),
This_Year = Number.ToText(Date.Year(DateTime.LocalNow())),
Prev_Years = Number.ToText(Date.Year(DateTime.LocalNow()) - 3),
Prev_Date = Text.Replace(Today, This_Year, Prev_Years),
body = "{
""filters"": {
""place_of_performance_locations"": [
{
""country"":""ETH""
}
],
""time_period"": [
{
""start_date"": ""2019-05-02"",
""end_date"": "& Text.From(#"Today") &"
}
],
""recipient_type_names"":[
""nonprofit""
]
},
""category"": ""cfda"",
""limit"": 10,
""page"": 1
}
",
Data= Web.Contents("https://api.usaspending.gov",[RelativePath = "api/v2/search/spending_by_category/cfda",Headers=[#"Content-Type"="application/json"],Content=Text.ToBinary(body)]),
DataRecord = Json.Document(Data),
Source=DataRecord,
results = Source[results],
#"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"amount", "code", "id", "name"}, {"Column1.amount", "Column1.code", "Column1.id", "Column1.name"}),
#"Added Custom" = Table.AddColumn(#"Expanded Column1", "Country", each "Ethiopia")
in
#"Added Custom"
From the above code, the part I am having a problem with is passing the Today variable into the API call (Body Variable), see here for the specific section:
""time_period"": [
{
""start_date"": ""2019-05-02"",
""end_date"": "& Text.From(#"Today") &"
}
],
Am I perhaps referencing the variable incorrectly? The call works perfectly when I hardcode the API call end date ("YYYY-MM-DD" format)
Thanks!
-Noah
You will need to change as per this (Hence, you will need to change " to """)
""end_date"": """&Text.From(#"Today")&"""
1 Reply
- Vijay_A_VermaMost Valuable Professional
You will need to change as per this (Hence, you will need to change " to """)
""end_date"": """&Text.From(#"Today")&"""