Multiple filter on source files

[NumeritasMartin]

Hello team,

I have a scenario for a small dashboard, the client will have a folder with multiple quarterly forecasts, both for that quarters forecast (so iterations) and for all quarters (so Q1, Q2, Q3 etc.)

I would like to filter the source files so that I only get a single copy of each quarters file. My criteria are as follows and will mostly be defined in the file name.

Quarter Name (defined in name)

Date (defined in name)

Version (defined in name)

(having both date and version may be superfluous)

So the filter would need to something that filters each quarter name to the latest version of date, let's presume version as that may be easier to define.

Example file name

Q1_15032021_V1.0.xlsx

I am unsure how to approach this multiple filter with dynamic criteria problem.

Thank you

Martin

[NumeritasMartin]

Hi @Vera_33 ,

Thnaks for taking the time to look at my query.

I understand what you have proposed but it isn't giving me the solution I need, I do still need to retain some of the other columns, for example file name, and this causes my group by to be populated with two rows.

See attached.

MO11 is the test with two versions being defined by the date. I used the Max function in the GROUPBY function on the date column I separated out.

Anything else you could suggest?

Martin

[Vera_33 · Accepted solution]

Hi @NumeritasMartin 

If I understand it correctly, you want only one row of MO11 with the max date, you can groupby to find the max then join back...if you have more logic/condition to apply, you can add a custom column then filter down. Hope it helps.

let
    Source = Folder.Files("C:\Users\Vera Zhu\Desktop\test"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "MO", each Text.BetweenDelimiters([Name],"_","_")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "version", each Text.BetweenDelimiters([Name],"_",".xlsx",1)),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"MO"}, {{"MAX", each List.Max([version]), type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"MO", "MAX"}, #"Added Custom1", {"MO", "version"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Content", "Name"}, {"Content", "Name"})
in
    #"Expanded Grouped Rows"

[Vera_33]

Hi @NumeritasMartin 

You can add Custom Columns to extract the value then GROUPBY, add another column to apply your logic

Table.AddColumn(#"Removed Columns", "Q", each Text.BeforeDelimiter( [Name],"_"))