Power Query to fillter rows from the most recent files from each quarter of the year

[nok]

Hi!

I created a table that combines multiple excel files in a Sharepoint folder, creating a single table in my PBI like this:

FileName	FileDate	Level	Value
file_03-02.2024	03/02/2024	DD	44
file_03-02.2024	03/02/2024	BB	33
file_09-01.2025	09/01/2025	AA	10
file_09-01.2025	09/01/2025	YY	12
file_02-02.2025	02/02/2025	FF	26
file_02-02.2025	02/02/2025	UU	65
file_26-03.2025	26/03/2025	BB	30
file_26-03.2025	26/03/2025	QQ	98
file_08-04.2025	08/04/2025	II	43
file_01-05.2025	01/05/2025	OO	50
file_22-06.2025	22/06/2025	DD	69
file_07-07.2025	07/07/2025	XX	70
file_13-08.2025	13/08/2025	ZZ	88
file_30-09.2025	30/09/2025	AA	34
file_30-09.2025	30/09/2025	VV	100
file_05-10.2025	05/10/2025	AA	77
file_05-10.2025	05/10/2025	EE	43
file_11-11.2025	11/11/2025	ZZ	73
file_23-12.2025	23/12/2025	BB	12
file_23-12.2025	23/12/2025	KK	51

In Power Query, I want to create a new column called "Year_Quarter" which indicates all rows from the most recent file for each quarter of the year, based on: Q1 (January 1 – March 31), Q2 (April 1 – June 30), Q3 (July 1 – September 30), and Q4 (October 1 – December 31). I want to do this because I want my table to filter and show only the rows from the most recent file for each quarter. The final result would be this:

FileName	FileDate	Level	Value	Year_Quarter
file_03-02.2024	03/02/2024	DD	44	2024-Q1
file_03-02.2024	03/02/2024	BB	33	2024-Q1
file_26-03.2025	26/03/2025	BB	30	2025-Q1
file_26-03.2025	26/03/2025	QQ	98	2025-Q1
file_22-06.2025	22/06/2025	DD	69	2025-Q2
file_30-09.2025	30/09/2025	AA	34	2025-Q3
file_30-09.2025	30/09/2025	VV	100	2025-Q3
file_23-12.2025	23/12/2025	BB	12	2025-Q4
file_23-12.2025	23/12/2025	KK	51	2025-Q4

How can I do this?

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Inserted year and quarter" = Table.AddColumn(Source, "Year-Quarter", each Text.From(Date.Year([FileDate]))&" Q-"&Text.From(Date.QuarterOfYear([FileDate]))),
    #"Grouped Rows" = Table.Group(#"Inserted year and quarter", {"Year-Quarter"}, {{"Count", each Table.SelectRows(Table.AddRankColumn(_,"Rank",{"FileDate", Order.Descending},[RankKind = RankKind.Competition]),each [Rank]<2)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"FileName", "FileDate", "Level   ", "Value  "}, {"FileName", "FileDate", "Level   ", "Value  "})
in
    #"Expanded Count"

Hope this helps.

[PhilipTreacy]

Hi @nok 

Why does your expected result include files from Feb?  if you only want the most recent file from each quarter then these should be ommitted, and only a file from Mar included for Q1.

Also, why are there more than 1 file per quarter?  You have said you want the most recent file, singular, but you have included more than 1 file per quarter?  Why?

Regards,

Phil

[lbendlin]

What's your definition of a quarter?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZLJagMxDIZfpcw5rhavc0xIAiGHkENCFoaeWij0/c+Vp7arKRQ8DD/IfBK/ludz+Pj8en9Da5BfGdm9yDesBrSADOVB4u1WxLlhWvVmbDYi1qqM0SDlDJ/pEZCgBOu1CGEPer9nlBXKxceMcvGQg/1ehEMPermIBP+LcjBoK8oBpLkS/LSFPej5LDImZSAZdM1AAnQVPRzyePWwyKBvKAH6ip5OIl4bkLZCMyBthYrOSwujqhoNxlY1gvwluN1EoqpKst9UUZLdpoo+HiJJtWXR4FhRiyAL04u1rge9Xucb0EfgDWEz64FwUTbGHnS3+zNYIkPttIiAaNFXVChbQ+1e2ALx4gj0Ff6PHo95XTRM0zc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FileName = _t, FileDate = _t, #"Level   " = _t, #"Value  " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FileName", type text}, {"FileDate", type date}, {"Level   ", type text}, {"Value  ", Int64.Type}},"en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Quarter", each Text.From(Date.Year([FileDate])) & "-Q" & Text.From(Date.QuarterOfYear([FileDate])),type text),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Quarter"}, {{"Max Date", each List.Max([FileDate]), type nullable date}, {"Rows", each _, type table [FileName=nullable text, FileDate=nullable date, #"Level   "=nullable text, #"Value  "=nullable number, Quarter=text]}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"FileName", "FileDate", "Level   ", "Value  "}, {"FileName", "FileDate", "Level   ", "Value  "}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Rows", each [FileDate] = [Max Date]),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max Date"})
in
    #"Removed Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

[GeraldGEmerick]

@nok You can create a custom column with the following formula:

= Text.From(Date.Year([FileDate])) & "-Q" & Text.From(Date.QuarterOfYear([FileDate]))