Hello all!
Sorry for the long post but I figured I would be as detailed as possible.
I am having an issue with an API call in a Dataflow and the flows incremental updates. If i do this in a normal Dataset it works fine but moving to a dataflow I get errors. The API requires a start_date and end_date parameter for polling data. In the dataset version I created the parameters, set Start to 05/01/2022 and End to 05/07/2022 and it pulled data fine. Then I enabled Incremental Updates on the dataset version to keep 6 quarters of data and refresh the last 7 days. After publishing the dataset and refreshing it worked as expected and pull 6 quarters of data and refreshed to today.
Now I am trying to move this to a dataflow. The M code is below. I found that I don't need the RangeStart and RangeEnd parameters as the flow will create that when incremental refreshes are setup. I thought GREAT but issue is I can’t save the query to enable the refreshes without a valid value so I took my original RangeStart and RangeEnd parameters and renamed them to RStart and REnd and changed the two lines of code to reflect the new name.
Once I did that I could save the flow and enable incremental refreshes. I then went back in and changed the RStart and REnd variables in the M code to RagneStart and RangeEnd thinking when the refreshes happen, the system will just plug the dates into it. By default the RagngeStart is set to 0001,01,01 and RangeEnd is set to 9999,12,31. When I try to save the query I get an error from the API that 0001,01,01 and 9999,12,31 are not valid date ranges and I can’t close and save the query. If I try to edit the RangeStart and RangeEnd to reflect 2022,05,01 and 2022,05,07 respectively, the table error is gone and the API is happy but I get an internal error as shown below.
Basically I am stuck and just can’t figure this out. Can anyone shed some light on this or point me in the right direction?
Error when I change the date in the RangeStart and RangeEnd parameters to get rid of the API error:
Activity ID: 317e9442-b39a-4b0f-9511-d58ab4b768e5
Request ID: 70c77567-9677-206d-3f18-613fd1fa0c9c
Correlation ID: 8102d84b-1b11-7035-fdbd-3e1f91a1c827
Status code: 500
Time: Mon May 16 2022 11:09:29 GMT-0400 (Eastern Daylight Time)
Service version: 13.0.18218.51
Client version: 2205.1.11282-train
Cluster URI: https://wabi-west-us-b-primary-redirect.analysis.windows.net/
M Code:
let
Source = Json.Document(Web.Contents("https://app.cloudability.com/", [RelativePath="api/1/reporting/cost/run", Query=[
filters = "category3==12345,category3==23456,category3==34567,category3==45678,category3==56789,category3==67890",
dimensions = "vendor,usage_family,usage_type,account_identifier,account_name,category3,date,vendor_account_name,tag11,tag2",
metrics = "total_amortized_cost",
start_date = Date.ToText(DateTime.Date(RangeStart)), <=Was a defined Parameter Pre-Incremental Update Enablment
end_date = Date.ToText(DateTime.Date(RangeEnd)) <=Was a defined Parameter Pre-Incremental Update Enablment
], Headers=[Authorization=APIKey]])), <=Defined Parameter
results = Source[results],
Custom = Source,
Navigation = Custom[results],
#"Converted to table" = Table.FromList(Navigation, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to table", "Column1", {"vendor", "usage_family", "usage_type", "account_identifier", "account_name", "category3", "date", "vendor_account_name", "tag11", "tag2", "total_amortized_cost"}, {"vendor", "usage_family", "usage_type", "account_identifier", "account_name", "category3", "date", "vendor_account_name", "tag11", "tag2", "total_amortized_cost"}),
#"Renamed columns" = Table.RenameColumns(#"Expanded Column1", {{"tag11", "Databrick Clusters"}, {"tag2", "Application"}, {"category3", "Cost Center"}, {"vendor", "Vendor"}, {"usage_family", "Usage Family"}, {"usage_type", "Usage Type"}, {"account_identifier", "Account Identifier"}, {"account_name", "Account Name"}, {"date", "Date"}, {"vendor_account_name", "Subscription"}, {"total_amortized_cost", "Total Amitorized Cost"}}),
#"Changed column type" = Table.TransformColumnTypes(#"Renamed columns", {{"Vendor", type text}, {"Usage Family", type text}, {"Usage Type", type text}, {"Account Identifier", type text}, {"Account Name", type text}, {"Cost Center", type text}, {"Date", type datetime}, {"Subscription", type text}, {"Databrick Clusters", type text}, {"Application", type text}, {"Total Amitorized Cost", type number}}),
#"Cloud Costs-44617465-autogenerated_for_incremental_refresh" = Table.SelectRows(#"Changed column type", each DateTime.From([Date]) >= RangeStart and DateTime.From([Date]) < RangeEnd)
in
#"Cloud Costs-44617465-autogenerated_for_incremental_refresh"
