combine csv with condition

[Anonymous]

Hello, I have folder in sharepoint with many files updated daily.

I need only csv starting with ABC and having date in the end (_YYYYMMDD.csv)

In addition to that if I have _20210115.csv and  _20210116.csv I will take only 20210116 because it contains data for previous days.

However If I have 20210131.csv and 20210201.csv - I will take both, and I will always keep the file for the end of each month for all years.

So I need a code in M (advanced editor).

Thank you for any advice

[v-jingzhang · Accepted solution]

Hi @Anonymous 

@AlexisOlson 's solution is great. Have you tried that? 

I'd just like to make some complement on his solution per your need. The complement are:

1. Add a step to filter out rows with errors after extracting date values. File names that don't end with (_YYYYMMDD.csv) have these errors.

2. Add a step to filter out rows that don't start with "ABC".

Here are the full codes.

let
    Source = SharePoint.Files("https://xxxxxxxxxx.sharepoint.com/sites/xxxxxxxxxx", [ApiVersion = 15]),
    #"Removed Other Columns1" = Table.SelectColumns(Source,{"Name", "Content"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns1", "Date", each Date.FromText(Text.End(Text.BeforeDelimiter([Name], ".csv"),8)), type date),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Errors", each Text.StartsWith([Name], "ABC")),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "EoM", each Date.EndOfMonth([Date]), type date),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"EoM"}, {{"Alldata", each Table.Max(_, "Date"), type table [Name=text, Content=binary, Date=date, EoM=date]}}),
    #"Expanded Alldata" = Table.ExpandRecordColumn(#"Grouped Rows", "Alldata", {"Name", "Content", "Date"}, {"Name", "Content", "Date"})
in
    #"Expanded Alldata"

Result

Then, you can click on the Combine Files icon on Content column. 

Best Regards,
Community Support Team _ Jing

[AlexisOlson]

You can extract the date string and turn it into a date column like this:

= Table.AddColumn(Source, "Date", each
    Date.FromText(Text.End(Text.BeforeDelimiter([Column1], ".csv"),8)),
    type date)

Then define and end of month date:

= Table.AddColumn(#"Added Custom", "EoM", each Date.EndOfMonth([Date]), type date)

Group over this column taking the maximum [Date] for each group.

= Table.Group(#"Added Custom1", {"EoM"},
    {{"AllRows", each Table.Max(_, "Date"),
    type table [Column1=nullable text, Date=date, EoM=date]}})

Then expand the remaining rows.

Full example M code you can paste into the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WijcyMDI0MDQ01UsuLlOK1YlWUoAJmcGFIALGhqgCRgYIgcR4qJAlRCgWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Date", each Date.FromText(Text.End(Text.BeforeDelimiter([Column1], ".csv"),8)), type date),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "EoM", each Date.EndOfMonth([Date]), type date),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"EoM"}, {{"AllRows", each Table.Max(_, "Date"), type table [Column1=nullable text, Date=date, EoM=date]}}),
    #"Expanded AllRows" = Table.ExpandRecordColumn(#"Grouped Rows", "AllRows", {"Column1"}, {"Column1"})
in
    #"Expanded AllRows"