Assign an order to incoming forecasts

Hi sara11 ,

I took these steps to reproduce your "Forecast no." column.

1) Sort by a) Forecast day date ascending then b) Date forecast was received.
2) Group by Forecast day date using an "All Rows" aggregration.
3) Add an index column to each table in the "All Rows" column.
4) Expand the grouping.
5) Add a "Test result" column to verify I got the results you seek.

I pasted your data into a table in Excel and loaded it in to Power Query. Here is the M code from that point onwards - where I write #"Previous Step" you will need to substitute the name of your previous step.

#"Sorted Rows" = Table.Sort(#"Previous Step",
   {{"Forecast day date", Order.Ascending}, {"Date forecast was received", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", 
   {"Forecast day date"}, {{"All Rows", each _, type table [Date forecast was received=nullable date, Forecast day date=nullable date, #"Forecast no. (sara 11)"=nullable Int64.Type]}}),
#"Add Forecast No." = Table.TransformColumns( #"Grouped Rows", 
   {{"All Rows", each Table.AddIndexColumn(_, "Forecast no.", 1, 1, Int64.Type)}}),
#"Expanded All Rows" = Table.ExpandTableColumn(#"Add Forecast No.", 
   "All Rows", {"Date forecast was received", "Forecast no. (sara 11)", "Forecast no."}, {"Date forecast was received", "Forecast no. (sara 11)", "Forecast no."}),
#"Added Custom" = Table.AddColumn(#"Expanded All Rows", 
   "Test Result", each if ([#"Forecast no. (sara 11)"]=[#"Forecast no."]) then "Pass" else "Fail"),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",
   {{"Date forecast was received", type date}, {"Forecast no. (sara 11)", Int64.Type}, {"Forecast no.", Int64.Type}, {"Test Result", type text}})

Hope this helps.

Hi sara11,

I took a slightly different approach this time - I think it's got greater clarity. Here's my thinking:
1) For each locality for each forecast day and hour, group its data into a table.
2) For each of those tables, sort them by forecast day ascending.
3) For each of those tables, add an index column. This becomes the forecast number.
4) Expand the tables.
I added a couple of extra steps
1) I added a test column to compare my answer to the answer in your sample.
2) I filtered for "Fails".

Here's the M code. The column names in my sample data were "Forecast received", "Forecast day", "Local", "Hour", "Temperature" and "Forecast no.". My extra steps may be deleted and you can edit the code to use column names of your choice (e.g. I use "Forecast no. GC" which you'll want to change).

let
   Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
   #"Changed Type" = Table.TransformColumnTypes(Source,{{"Forecast received", type date}, {"Forecast day", type date}, {"Local", type text}, {"Hour", Int64.Type}, {"Temperature", type number}, {"Forecast no.", Int64.Type}}),
   Group = Table.Group(#"Changed Type", 
      {"Forecast day", "Local", "Hour"}, 
      {{"All Rows", each _, type table [Forecast received=nullable date, Forecast day=nullable date, Local=nullable text, Hour=nullable number, Temperature=nullable number, #"Forecast no."=nullable number]}}),
   #"Sort by Forecast Day" = Table.TransformColumns( Group, 
      {{"All Rows",each Table.Sort(_,{"Forecast received", Order.Ascending})}}),
   #"Add Forecast Number" = Table.TransformColumns( #"Sort by Forecast Day", 
      {{"All Rows", each Table.AddIndexColumn(_,"Forecast no. GC",1,1,Int64.Type)}}),
   #"Expanded All Rows" = Table.ExpandTableColumn(#"Add Forecast Number", 
      "All Rows", 
      {"Forecast received", "Temperature", "Forecast no.", "Forecast no. GC"}, {"Forecast received", "Temperature", "Forecast no.", "Forecast no. GC"}),
   #"Changed Type1" = Table.TransformColumnTypes(#"Expanded All Rows",
      {{"Forecast received", type date}, {"Temperature", type number}, {"Forecast no.", Int64.Type}, {"Forecast no. GC", Int64.Type}}),
   #"Added Test Column" = Table.AddColumn(#"Changed Type1", 
      "Test", 
      each if ( [#"Forecast no."] = [Forecast no. GC] ) then "Pass" else "Fail", type text),
   #"Filtered For Fails" = Table.SelectRows(#"Added Test Column", 
      each ([Test] = "Fail"))
in
   #"Filtered For Fails"

 Hope this helps.