Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Claire Barr]

Hello, I’m a brand squeaky new user to Power Query M, and am struggling with creating two custom columns.

I am working with the hourly dataset from The Weather Channel for multiple weather station locations, and I need return a table with the GasDate, Location, TempF, HDD, HDDs_DB (prior day HDD), and RA7_HDDs (rolling 7-day average HDD). For reference, HDD stands for Heating Degree Day, a term used in the gas industry to quantify how much colder it is than 65F.

So far I have created this on my own (some irrelevant rows of code have been removed), which groups the data by gasdate and weatherstation location, averages the temperature in Fahrenheit, and calculates the HDD:

let

Source = Sql.Database(“WeatherDataTest”),

dbo_TWChistorical = Source{[Schema=”dbo”,Item=”TWChistorical”]}[Data],

#”Grouped Rows” = Table.Group(#”Removed Other Columns”, {“GasDate”, “Location”}, {{“TempF”, each List.Average([apparentTemperatureFahrenheit]), type number}}),

#”Sorted Rows” = Table.Sort(#”Grouped Rows”,{{“GasDate”, Order.Descending}}),

#”Rounded Off” = Table.TransformColumns(#”Sorted Rows”,{{“TempF”, each Number.Round(_, 0), type number}}),

#”Added HDD” = Table.AddColumn(#”Rounded Off”, “HDD”, each if [TempF]<65 then 65-[TempF] else 0)
in

#”Added HDD”
 

but now I need to add a column that returns the prior day’s HDD value, and a column for the rolling 7-day average of HDD, which I am struggling with due to the multiple locations.

Thank you for any and all help.

——————————
Claire B.
Analyst
——————————

[Greg Philps]

Hi Claire. As with many things in Power BI, there are often many ways of doing the same thing. One suggestion would be to create your HDDs_DB and RA7_HDD fields as calculated columns using DAX. Here’s a small sample that hopefully you’ll find useful. Good luck. Greg

——————————
Greg Philps
Power BI Consultant
Ottawa ON Canada
——————————
——————————————-