Forum Discussion
Shared dataset with incremental refresh growing rapidly
- 4 years ago
I mananged to solve the issue with modifying query for the table. Following two steps were removed:
#"Inserted Date" = Table.AddColumn(TableData, "Date", each DateTime.Date([DatetimeLocal]), type date), #"Inserted Hour" = Table.AddColumn(#"Inserted Date", "Hour", each Time.Hour([DatetimeLocal]), Int64.Type),Instead of power query, these are now calculated in DAX (after data is loaded from database). Hint for this change was warning message (shown on previous screenshot) saying Unable to confirm if the M query can be folded. Now the query is clean and dataset growth is smaller. Final query:
let Source = Sql.Databases(DB_Server), Table = Source{[Name=DB]}[Data], TableData = Table{[Schema="someschema",Item="sometable"]}[Data], #"Filtered Rows" = Table.SelectRows(TableData, each [DatetimeUtc] > RangeStart and [DatetimeUtc] <= RangeEnd) in #"Filtered Rows"
I mananged to solve the issue with modifying query for the table. Following two steps were removed:
#"Inserted Date" = Table.AddColumn(TableData, "Date", each DateTime.Date([DatetimeLocal]), type date),
#"Inserted Hour" = Table.AddColumn(#"Inserted Date", "Hour", each Time.Hour([DatetimeLocal]), Int64.Type),Instead of power query, these are now calculated in DAX (after data is loaded from database). Hint for this change was warning message (shown on previous screenshot) saying Unable to confirm if the M query can be folded. Now the query is clean and dataset growth is smaller. Final query:
let
Source = Sql.Databases(DB_Server),
Table = Source{[Name=DB]}[Data],
TableData = Table{[Schema="someschema",Item="sometable"]}[Data],
#"Filtered Rows" = Table.SelectRows(TableData, each [DatetimeUtc] > RangeStart and [DatetimeUtc] <= RangeEnd)
in
#"Filtered Rows"