Forum Discussion
combine csv with condition
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
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
2 Replies
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"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
