Forum Discussion

nhaglund's avatar
nhaglund
New Member
4 years ago
Solved

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_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional

    You will need to change as per this (Hence, you will need to change " to """)

    ""end_date"": """&Text.From(#"Today")&"""