I have a query that works succesfully in Power Query editor, but generates the following error when applying/loading it to the report:
Error returned: 'M Engine error: 'Microsoft.Data.Mashup.Preview; Token Identifier expected.'. '.
I beleive this may have to do with not having the RelativePath portion be in quotes, but I can't figure out how to put quotes around this section without throwing off the nested let section for the POST API call. Here is my code:
let
//Load the file into a buffer
Test = Binary.Buffer(Web.Contents("https://files.XXXXX.gov/generated_downloads",
[RelativePath=
let
url = "https://api.XXXXX.gov/api/v2/bulk_download/awards/",
body = "{
""filters"": {
""prime_award_types"":[
""A"",
""B"",
""C"",
""D"",
""IDV_A"",
""IDV_B"",
""IDV_B_A"",
""IDV_B_B"",
""IDV_B_C"",
""IDV_C"",
""IDV_D"",
""IDV_E""
],
""agencies"": [
{
""type"": ""funding"",
""tier"": ""toptier"",
""name"": ""X of X""
}
],
""date_type"":""action_date"",
""date_range"":{
""start_date"":""2021-10-01"",
""end_date"":""2022-09-30""
}
}
}",
Source = Json.Document(Web.Contents(url,[Content=Text.ToBinary(body),Headers=[#"Content-Type"="application/json"]])),
#"Converted to Table" = Record.ToTable(Source),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "file_url")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Removed Columns", {{"Value", each Text.AfterDelimiter(_, "/", 3), type text}}),
Custom1 = #"Extracted Text After Delimiter"{0}[Value]
in
Custom1]
)
)
in
Test
