I have a query (actually group of queries) that give me daily info as to energy consumption, based on hourly data from past 5 years from my thermostat.
Works fine, but is rather silly that I'm recalculating all the old data each time I update rather than just add new data to existing data.
I've copied the data form those queries to a separate table and now want to build a query that will just add data for recent days (ie since last update).
I can't see how to filter the 5-year data to remove data for dates already present in my Excel table. Can M language access a cell in excel sheet ?
Hi Trevor,
There's no incremental refresh in Power Query for Excel yet. It's in preview in Power BI's version of Power Query, so it's coming. We just don't have it yet. For now you need to get all of the data again with each refresh.
Mynda
Thanks Mynda. Actually I had not considered incremental refresh, but filtering out the data rows I already had before performing all the processing (which is what takes up the time). Been going through your PQ course and it looks to me as if I can accomplish that with parameters (ch 7). basically, import all the data and then filter out only the new rows. Then copy that new data to a separate master table with a macro.
I should be able to discard "old" rows at around line 17 in code below, right?
let
Source = Csv.Document(File.Contents("C:UsersTrevorDropboxDocuments_AnconalaanToonToonDataelec_quantity_lt_CurrentElectricityQuantity_5yrhours.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Column1"}, #"Elec Hi", {"Column1"}, "Elec Hi", JoinKind.LeftOuter),
#"Expanded Elec Hi" = Table.ExpandTableColumn(#"Merged Queries", "Elec Hi", {"Column2"}, {"Elec Hi.Column2"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Elec Hi", {"Column1"}, #"Gas Used", {"Column1"}, "Gas Used", JoinKind.LeftOuter),
#"Expanded Gas Used" = Table.ExpandTableColumn(#"Merged Queries1", "Gas Used", {"Column2"}, {"Gas Used.Column2"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded Gas Used", {"Column1"}, #"WW El Used", {"Column1"}, "WW El Used", JoinKind.LeftOuter),
#"Merged Queries3" = Table.NestedJoin(#"Merged Queries2", {"Column1"}, #"WW Energy Out", {"Column1"}, "WW Energy Out", JoinKind.LeftOuter),
#"Expanded WW Energy Out" = Table.ExpandTableColumn(#"Merged Queries3", "WW Energy Out", {"Column2"}, {"WW Energy Out.Column2"}),
#"Expanded WW El Used" = Table.ExpandTableColumn(#"Expanded WW Energy Out", "WW El Used", {"Column2"}, {"WW El Used.Column2"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded WW El Used",{{"Column2", "Elec Lo1"}, {"Elec Hi.Column2", "Elec Hi1"}, {"Gas Used.Column2", "Gas Used"}, {"WW Energy Out.Column2", "WW Energy Out"}, {"WW El Used.Column2", "WW El Used"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Column1", "WW El Used", "WW Energy Out", "Elec Lo1", "Elec Hi1", "Gas Used"}),
#"Added Custom" = Table.AddColumn(#"Reordered Columns", "Date", each #datetime(1970, 1, 1, 0, 0, 0 ) + #duration(0, 0, 0, [Column1])),
#"Extracted Date" = Table.TransformColumns(#"Added Custom",{{"Date", DateTime.Date, type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Date",{"Column1"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"Date", "WW El Used", "WW Energy Out", "Elec Lo1", "Elec Hi1", "Gas Used"}),
filter the data at this point....
#"Added Index" = Table.AddIndexColumn(#"Reordered Columns1", "Index", 0, 1),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
#"Merged Queries4" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries4", "Added Index1", {"WW El Used", "WW Energy Out", "Elec Lo1", "Elec Hi1", "Gas Used"}, {"Added Index1.WW El Used", "Added Index1.WW Energy Out", "Added Index1.Elec Lo1", "Added Index1.Elec Hi1", "Added Index1.Gas Used"}),
#"Sorted Rows" = Table.Sort(#"Expanded Added Index1",{{"Index", Order.Ascending}}),
#"Added Custom1" = Table.AddColumn(#"Sorted Rows", "WW Elec", each [WW El Used]-[Added Index1.WW El Used]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "WW Energy Out.1", each [WW Energy Out]-[Added Index1.WW Energy Out]),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "ElecLo hourly", each [Elec Lo1]-[Added Index1.Elec Lo1]),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "ElecHi Hourly", each [Elec Hi1]-[Added Index1.Elec Hi1]),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Gas Hourly", each [Gas Used]-[Added Index1.Gas Used]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom5",{"Added Index1.Elec Hi1", "WW El Used", "WW Energy Out", "Elec Lo1", "Elec Hi1", "Gas Used", "Index", "Index.1", "Added Index1.WW El Used", "Added Index1.WW Energy Out", "Added Index1.Elec Lo1", "Added Index1.Gas Used"}),
#"Grouped Rows" = Table.Group(#"Removed Columns1", {"Date"}, {{"Power-In", each List.Sum([WW Elec]), type number}, {"Heat-Out", each List.Sum([WW Energy Out.1]), type number}, {"Elec Lo", each List.Sum([ElecLo hourly]), type number}, {"Elec Hi", each List.Sum([ElecHi Hourly]), type number}, {"Gas", each List.Sum([Gas Hourly]), type number}}),
#"Divided Column1" = Table.TransformColumns(#"Grouped Rows", {{"Heat-Out", each _ / 3.6, type number}}),
#"Rounded Off" = Table.TransformColumns(#"Divided Column1",{{"Heat-Out", each Number.Round(_, 1), type number}}),
#"Sorted Rows1" = Table.Sort(#"Rounded Off",{{"Date", Order.Descending}}),
#"Divided Column2" = Table.TransformColumns(#"Sorted Rows1", {{"Gas", each _ / 1000, type number}}),
#"Rounded Off1" = Table.TransformColumns(#"Divided Column2",{{"Gas", each Number.Round(_, 2), type number}}),
#"Rounded Off2" = Table.TransformColumns(#"Rounded Off1",{{"Power-In", each Number.Round(_, 1), type number}}),
#"Divided Column" = Table.TransformColumns(#"Rounded Off2", {{"Elec Lo", each _ / 1000, type number}}),
#"Divided Column3" = Table.TransformColumns(#"Divided Column", {{"Elec Hi", each _ / 1000, type number}}),
#"Rounded Off3" = Table.TransformColumns(#"Divided Column3",{{"Elec Hi", each Number.Round(_, 1), type number}}),
#"Rounded Off4" = Table.TransformColumns(#"Rounded Off3",{{"Elec Lo", each Number.Round(_, 1), type number}}),
#"Added Custom6" = Table.AddColumn(#"Rounded Off4", "EleTot", each [Elec Lo]+[Elec Hi]),
#"Reordered Columns2" = Table.ReorderColumns(#"Added Custom6",{"Date", "Power-In", "Heat-Out", "Elec Lo", "Elec Hi", "EleTot", "Gas"})
in
#"Reordered Columns2"
Hi Trevor,
I misunderstood your process and to be honest, I still don't really understand it even with the M code, as it's very difficult to read through the code and hold a picture in your head of data you've never seen to try and visualise what you're trying to achieve, sorry.
Unless the queries are running unbearably slow, I would just let it refresh all of the data each time, as getting macros involved kind of defeats the purpose of setting up the query so you can just refresh and be done.
Mynda
Thanks to your PQ course 
, I've managed to set up a filter to discard the unwanted data before the processing stage - now much quicker. I have to use macros for some of the other data anyway... Happy Bunny!
Glad you got it working! It's always better when you figure things out for yourself...you're more likely to remember it then 🙂
