Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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

21122103.jpg

 

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

 

Best Regards,
Community Support Team _ Jing

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

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

21122103.jpg

 

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

 

Best Regards,
Community Support Team _ Jing

AlexisOlson
Super User
Super User

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"

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors