Assign an order to incoming forecasts

[sara11]

My problem is this: every day I receive weather forecasts for the next 5 days. Something like this:
On the 1st of April I get the forecasts for the 2nd, 3rd, 4th, 5th and 6th of April. On the 2nd of April I get forecasts for the 3rd, 4th, 5th, 6th and 7th. And so on. Except for Sunday, which I don't get any forecast at all. I want to assign an order to the arrival of the forecasts.
So it would look something like this:

Date forecast was received	Forecast day date	Forecast no.
01/04/2023	02/04/2023	1
01/04/2023	03/04/2023	1
01/04/2023	04/04/2023	1
01/04/2023	05/04/2023	1
01/04/2023	06/04/2023	1
02/04/2023	03/04/2023	2
02/04/2023	04/04/2023	2
02/04/2023	05/04/2023	2
02/04/2023	06/04/2023	2
02/04/2023	07/04/2023	1
03/04/2023	04/04/2023	3
03/04/2023	05/04/2023	3
03/04/2023	06/04/2023	3
03/04/2023	07/04/2023	2
03/04/2023	08/04/2023	1
04/04/2023	05/04/2023	4
04/04/2023	06/04/2023	4
04/04/2023	07/04/2023	3
04/04/2023	08/04/2023	2
04/04/2023	09/04/2023	1
05/04/2023	06/04/2023	5
05/04/2023	07/04/2023	4
05/04/2023	08/04/2023	3
05/04/2023	09/04/2023	2
05/04/2023	10/04/2023	1
06/04/2023	07/04/2023	5
06/04/2023	08/04/2023	4
06/04/2023	09/04/2023	3
06/04/2023	10/04/2023	2
06/04/2023	11/04/2023	1
08/04/2023	09/04/2023	4
08/04/2023	10/04/2023	3
08/04/2023	11/04/2023	2
08/04/2023	12/04/2023	1
08/04/2023	13/04/2023	1

Well, I would like to get a measure or a custom column in power query editor that would calculate me this order of reception of the forecasts.

Thanks in advance for all the help.

[collinsg · Accepted solution]

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.

[collinsg · Accepted solution]

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.

[sara11]

collinsg,

Thank you very much for your answer. Indeed, your solution works. Unfortunately, I simplified my table in the belief that I would be able to reproduce the code for my situation. However, I was not able to, so I turn to you again for help.
My table breaks down into an hourly forecast (24 forecasts per day) and different locations. Something like this:

Date forecast was received	Forecast day date	Local	Hour	Temperature	Forecast no.
01/04/2023	02/04/2023	A	0		1
01/04/2023	02/04/2023	A	1		1
01/04/2023	02/04/2023	A	2		1
01/04/2023	02/04/2023	A	3		1
01/04/2023	02/04/2023	A	4		1
01/04/2023	02/04/2023	A	5		1
01/04/2023	02/04/2023	A	6		1
01/04/2023	02/04/2023	A	7		1
01/04/2023	02/04/2023	A	8		1
01/04/2023	02/04/2023	A	9		1
01/04/2023	02/04/2023	A	10		1
01/04/2023	02/04/2023	A	11		1
01/04/2023	02/04/2023	A	12		1
01/04/2023	02/04/2023	A	13		1
01/04/2023	02/04/2023	A	14		1
01/04/2023	02/04/2023	A	15		1
01/04/2023	02/04/2023	A	16		1
01/04/2023	02/04/2023	A	17		1
01/04/2023	02/04/2023	A	18		1
01/04/2023	02/04/2023	A	19		1
01/04/2023	02/04/2023	A	20		1
01/04/2023	02/04/2023	A	21		1
01/04/2023	02/04/2023	A	22		1
01/04/2023	02/04/2023	A	23		1
01/04/2023	02/04/2023	A	24		1
01/04/2023	03/04/2023	A	0		1
01/04/2023	03/04/2023	A	1		1
01/04/2023	03/04/2023	A	2		1
01/04/2023	03/04/2023	A	3		1
01/04/2023	03/04/2023	A	4		1
01/04/2023	03/04/2023	A	5		1
01/04/2023	03/04/2023	A	6		1
01/04/2023	03/04/2023	A	7		1
01/04/2023	03/04/2023	A	8		1
01/04/2023	03/04/2023	A	9		1
01/04/2023	03/04/2023	A	10		1
01/04/2023	03/04/2023	A	11		1
01/04/2023	03/04/2023	A	12		1
01/04/2023	03/04/2023	A	13		1
01/04/2023	03/04/2023	A	14		1
01/04/2023	03/04/2023	A	15		1
01/04/2023	03/04/2023	A	16		1
01/04/2023	03/04/2023	A	17		1
01/04/2023	03/04/2023	A	18		1
01/04/2023	03/04/2023	A	19		1
01/04/2023	03/04/2023	A	20		1
01/04/2023	03/04/2023	A	21		1
01/04/2023	03/04/2023	A	22		1
01/04/2023	03/04/2023	A	23		1
01/04/2023	03/04/2023	A	24		1

The table then continues for the rest of the days and locations.

collinsg, do you think you can adapt your code to this situation?
Thank you very much in advance for your help.

[collinsg · Accepted solution]

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.