Forum Discussion
Incremental Refresh with Non Native Query Transformations
Thanks Anonymous for the reference links. I should have clarified, my initial query can be folded. But subsequent transformations (after the folding and using RangeStart/RangeEnd) are performed. My question is what transformations after a query fold that would still cause the entire querying of the dataset (i.e. igore the query folding?)
query folding stops with the change type transformation:
let
StartDate = "'"&DateTime.ToText(RangeStart)&"'",
EndDate = "'"&DateTime.ToText(RangeEnd)&"'",
SCADATags= "(" & Text.Combine(List.Transform(#"3SCADAFlowTags"[SCADAID], each "'"&_&"'"),",") & ")",
Source = Odbc.Query("Driver={PostgreSQL ANSI(x64)};server=XXXX;port=5432;Database=XXXX", "SELECT * FROM XXXXX where scadaid in "&SCADATags&" and measurementdatetime >= "&StartDate&" and measurementdatetime < "&EndDate&" and measurementquality = '100'"),
//query folding stops
#"Changed Type" = Table.TransformColumnTypes(Source,{{"measurementvalue", type number}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"scadaid"}, #"3SCADAFlowTags", {"SCADAID"}, "SCADAFlowTags", JoinKind.FullOuter),
#"Expanded SCADAFlowTags1" = Table.ExpandTableColumn(#"Merged Queries", "SCADAFlowTags", {"Description", "FromServiceArea", "MeterError", "ToServiceArea"}, {"Description", "FromServiceArea", "MeterError", "ToServiceArea"}),
#"Added Custom" = Table.AddColumn(#"Expanded SCADAFlowTags1", "Date", each DateTime.Date([measurementdatetime])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"MeterError", type number}}),
#"Meter Error Calc" = Table.AddColumn(#"Changed Type2", "Multiplication", each -1*[measurementvalue]/(1+[MeterError]), type number),
#"Renamed Columns" = Table.RenameColumns(#"Meter Error Calc",{{"Multiplication", "measurementvaluefinal"}}),
#"Removed Duplicates1" = Table.Distinct(#"Renamed Columns", {"scadaid", "measurementvalue", "measurementdatetime","Description"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Duplicates1", each ([scadaid] <> null)),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"scadaid", Order.Ascending}, {"measurementdatetime", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
#"Removed Columns" = Table.RemoveColumns(#"Added Index",{"ToServiceArea","measurementtype","networkfacilityid","measurementquality","objectid"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"FromServiceArea", "ServiceArea"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Date", type date}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"MeterError"})
in
#"Removed Columns1"
Hi sedareports ,
I'm so sorry I'm not clear about your problem. Could you please provide more details on it?
Best Regards
Rena
- sedareports6 years agoAdvocate II
My question/problem is, can a user perform transformations after a query folding and still maintain incremental refresh? I.e. will the subseqent tranformations only be applied to new incrementally loaded data, or will they possible trigger a full query/processing of the entire data set.
For example.. a sorting transformation or applying an index.
- Anonymous6 years agoNot applicable
Hi sedareports ,
Hope the following documentations can help you.
Query Folding in Power Query to Improve Performance
Not Folding; the Black Hole of Power Query Performance
Best Regards
Rena