User Profile
OllieSvT
Regular Visitor
Joined 3 years ago
User Widgets
Contributions
Re: Incremental Refresh for Dataflow calling Source API Multiple Times
No worries at all, all guidance is super appreciated 🙂 I am still trying to make sense of this and found the relevant guidance pages on the Microsoft pages but won't lie that I am struggling with the correct syntax. Would you be able to demonstrate the structure of the query for the two Range sections and how to wrap the other parts of the url to this? ThanksRe: Incremental Refresh for Dataflow calling Source API Multiple Times
Potentially getting closer but also feel this may not be the best method.. I now have an issue where I need three batches of additional text to add on to create a full URL but I can only define the 'RelativePath' field name once in the query: let Url = "http://environment.data.gov.uk/hydrology/id/measures/5adcd239-4420-40b5-abe2-69082f9e24ff-rainfall-t-900-mm-qualified", URLExtension = "/readings.json&?mineq-date="&RangeStart&"&max-date="&RangeEnd&"&_limit=2000000", RangeTrial = "2023-10-10", RangeTrial2 = "2023-10-11", Source = Json.Document( Web.Contents( Url, [ RelativePath = "/readings.json?mineq-date=", Query = RangeTrial, RelativePath = "&max-date=", Query2 = RangeTrial2 ] ) ), I trialed with just the first query and a dummy start date (RangeTrial) this seemed to work but not sure how to build from here.Re: Incremental Refresh for Dataflow calling Source API Multiple Times
I did think this may be the case, I had originally tried to include the parameters as drynamic ranges in the url but get caught in a loop whereby I can't save the dataflow as I have dynamic ranges. I am confused where to go from here! Thanks let Url = "http://environment.data.gov.uk/hydrology/id/measures/5adcd239-4420-40b5-abe2-69082f9e24ff-rainfall-t-900-mm-qualified/readings.json?mineq-date="&RangeStart&"&max-date="&RangeEnd&"&_limit=2000000", Source = Json.Document(Web.Contents(Url)), Navigation = Source[items], #"Converted to table" = Table.FromList(Navigation, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Columns" = Table.ExpandRecordColumn(#"Converted to table", "Column1", {"dateTime", "date", "value", "completeness", "quality"}, {"DateTime", "Date", "Value", "completeness", "quality"}), #"Changed column type" = Table.TransformColumnTypes(#"Expanded Columns", {{"DateTime", type datetime}, {"Date", type date}, {"Value", type number}, {"completeness", type text}, {"quality", type text}}), #"Inserted time" = Table.AddColumn(#"Changed column type", "Time", each DateTime.Time([DateTime]), type nullable time), #"Added TID" = Table.AddColumn( #"Inserted time", "Time_ID", each Text.PadStart( Text.From(( Number.Round( Number.From( Time.From(Number.Round(Number.From(Time.From([DateTime]))*(24*60/15))/(24*60/15)) //Create rounded 15 min time interval (96 divisions for TimeslotID) ) *96) +1)) ,2,"0")), #"Added TSID" = Table.AddColumn(#"Added TID", "Timeslot_ID", each Text.Combine({ Text.From(Date.Year([DateTime])), Text.PadStart(Text.From(Date.Month([DateTime])),2,"0"), Text.PadStart(Text.From(Date.Day([DateTime])),2,"0"), Text.From([Time_ID]) })), #"Change TimeID type" = Table.TransformColumnTypes(#"Added TSID", {{"Timeslot_ID", Int64.Type}, {"Time_ID", Int64.Type}}), #"Removed columns" = Table.RemoveColumns(#"Change TimeID type", {"Time_ID"}), #"Reordered columns" = Table.ReorderColumns(#"Removed columns", {"DateTime", "Date", "Time", "Timeslot_ID", "Value", "completeness", "quality"}), #"Renamed columns" = Table.RenameColumns(#"Reordered columns", {{"Value", "Value (mm)"}, {"completeness", "Completeness"}, {"quality", "Quality"}}), #"Sorted rows" = Table.Sort(#"Renamed columns", {{"DateTime", Order.Descending}}), #"RF_Parkend_API-for_incremental_refresh" = Table.SelectRows( #"Sorted rows", each DateTime.From([DateTime]) >= DateTime.From(RangeStart) and DateTime.From([DateTime]) < DateTime.From(RangeEnd)) in #"RF_Parkend_API-for_incremental_refresh"Re: Incremental Refresh for Dataflow calling Source API Multiple Times
let Url = http://environment.data.gov.uk/hydrology/id/measures/5adcd239-4420-40b5-abe2-69082f9e24ff-rainfall-t-900-mm-qualified/readings.json?mineq-date=1920-01-01&max-date=2050-01-01&_limit=2000000, Source = Json.Document(Web.Contents(Url)), Navigation = Source[items], #"Converted to table" = Table.FromList(Navigation, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Columns" = Table.ExpandRecordColumn(#"Converted to table", "Column1", {"dateTime", "date", "value", "valid", "invalid", "missing", "completeness", "quality"}, {"DateTime", "Date", "Value", "valid", "invalid", "missing", "completeness", "quality"}), #"Changed column type" = Table.TransformColumnTypes(#"Expanded Columns", {{"DateTime", type datetime}, {"Date", type date}, {"Value", type number}, {"valid", Int64.Type}, {"invalid", Int64.Type}, {"missing", Int64.Type}, {"completeness", type text}, {"quality", type text}}), #"Inserted time" = Table.AddColumn(#"Changed column type", "Time", each DateTime.Time([DateTime]), type nullable time), #"Added TID" = Table.AddColumn( #"Inserted time", "Time_ID", each Text.PadStart( Text.From(( Number.Round( Number.From( Time.From(Number.Round(Number.From(Time.From([DateTime]))*(24*60/15))/(24*60/15)) //Create rounded 15 min time interval (96 divisions for TimeslotID) ) *96) +1)) ,2,"0")), #"Added TSID" = Table.AddColumn(#"Added TID", "Timeslot_ID", each Text.Combine({ Text.From(Date.Year([DateTime])), Text.PadStart(Text.From(Date.Month([DateTime])),2,"0"), Text.PadStart(Text.From(Date.Day([DateTime])),2,"0"), Text.From([Time_ID]) })), #"Change TimeID type" = Table.TransformColumnTypes(#"Added TSID", {{"Timeslot_ID", Int64.Type}, {"Time_ID", Int64.Type}}), #"Removed columns" = Table.RemoveColumns(#"Change TimeID type", {"Time_ID"}), #"Reordered columns" = Table.ReorderColumns(#"Removed columns", {"DateTime", "Date", "Time", "Timeslot_ID", "Value", "valid", "invalid", "missing", "completeness", "quality"}), #"RF_Parkend-incremental_refresh" = Table.SelectRows(#"Reordered columns", each DateTime.From([DateTime]) >= RangeStart and DateTime.From([DateTime]) < RangeEnd), #"RF_Parkend-4461746554696D65-autogenerated_for_incremental_refresh" = Table.SelectRows(#"RF_Parkend-incremental_refresh", each DateTime.From([DateTime]) >= RangeStart and DateTime.From([DateTime]) < RangeEnd) in #"RF_Parkend-4461746554696D65-autogenerated_for_incremental_refresh"Re: Incremental Refresh for Dataflow calling Source API Multiple Times
Hi cpwebb, I have been having the exact same issue as described by ismael. I checked my PQ online and can confirm the same thing happens even with the 'allow combioning...' option selected. I am connecting into a freely available database with an API. This has a fair usage limit on it which I am currently breaching due to the way PBI is pulling the data when using an incremental refresh. I have a temporary solution in place that works outside of PBI but I wish to develop an all encompassing dataflow for the long term and this process will be key in the long term stability of this. Any thoughts on how to improve the efficiency of the API/Incremental Refresh process is much appreciated. Thanks OllieHow do I calculate proportional values depending on multiple variables? - Rainfall data
I am attempting to create virtual rainfall data for multiple 'sites' using varying proportional combinations of three rain gauges. My data are held in a few tables/lists in the following formats: Site list: Station apportionment table (not yet processed, no data types selected etc): Daily rain fall data (summarised table from hourly data): I would like to define rainfall values in the format: Rainfall at site z = (rainfall at site z gauge 1 * proportion of gauge 1 for site z) + (rainfall at site z gauge 2 * proportion of gauge 2 for site z) + (rainfall at site z gauge 3 * proportion of gauge 3 for site z) I would like to have daily values to plot up whereby depending on a (single-select) site slicer the appropriate "virtual rainfall data" are plotted. The current data model is small 1 year of daily data, ~40 sites and 16 rain gauges. If I can find a clean solution I will look to expand this to 150+ sites and 10+ years of rainfall data (potentially at an hourly resolution). I would prefer to create this using DAX, but could potentially use a Power Query solution that creates a new rainfall dataset which I can directly link to the 'site' list. For a DAX solution, I can't work out how to relate the site list to the gauge apportionments (do I need to unpivot the second table to 3 columns, Site ID, Gauge ID, proportion? Then come up with some calculation that does the above equation? EDIT: To elaborate, my main query is: what is the most efficient measure to calculate the 'daily rainfall (mm/day)' for a given site, using the above equation? For example, at Audley, the three gauges in use are: Gauge No. Gauge Proportion 99093 0.625 99179 0.348 3330 0.027 Thus the estimated rainfall for Audley on a given date is: Audley Rainfall day n = (rainfall at 99093 on day n * 0.625) + (rainfall at 99179 on day n * 0.348)+ (rainfall at 3330 on day n * 0.027) As stated above, I need to calculate this for everyday of a ~10 year (hourly) dataset of rainfall, although some gauges do have isolated missing ranges (generally 98%+ complete). The end goal is a page with a couple plots on it, presenting the rainfall as calculated above against other internal timeseries data. These will be related by date-time and site ID. Any and all help is very appreciated. Thanks
