Forum Discussion

Manuel123's avatar
Manuel123
Helper I
5 years ago
Solved

Only load latest csv files in folder

Dear Helpers,

 

I'm a newbie to power bi and I hope that you can help me with the following issue:

I have several csv files in a folder with the following name convention (could be changed if needed):

Project_Date.csv

For example:

Project1_31.12.2020

Project2_02.01.2021

Project1_02.01.2021

Project3_03.01.2021

 

Now I would like to load only the most recent file of each project. With regard to the example this would be:

Project1_02.01.2021

Project2_02.01.2021

Project3_03.01.2021

 

Is this possible?

 

Thank you in advance for your help and kind regards

Manuel

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Manuel123 ,

    I created a sample pbix file for you, please check whether that is what you want.

    let
    Source = Folder.Files("x:\xx\xx\xx\xxx"),
    #"Added Custom" = Table.AddColumn(Source, "Projects", each Text.BeforeDelimiter([Name],"_")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Date", each Text.From(Text.AfterDelimiter([Name], "_"))),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom1",".csv","",Replacer.ReplaceText,{"Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Date", type date}}),
    #"Grouped Rows" =  Table.Group(#"Changed Type", {"Projects"}, 
                     {{"Latest Record", each Table.FirstN(Table.Sort(_, {{"Date", Order.Descending}}),1), type table [Content=binary, Name=text, Extension=text, Date accessed=nullable datetime, Date modified=nullable datetime, Date created=nullable datetime, Attributes=nullable record, Folder Path=text, Projects=text, Date=nullable date]}}),
    #"Expanded maxdate" = Table.ExpandTableColumn(#"Grouped Rows", "Latest Record", {"Attributes", "Content", "Date accessed", "Date created", "Date modified", "Extension", "Folder Path", "Name"}, {"Attributes", "Content", "Date accessed", "Date created", "Date modified", "Extension", "Folder Path", "Name"})
in
    #"Expanded maxdate"

    Best Regards

4 Replies

    • Manuel123's avatar
      Manuel123
      Helper I
      5 years ago

      amitchandak , thank you for the hint, however, this would not work as I do not only have to look after the date but also after the project to get the latest file of each project (the number of projects will increase over time). So I would need a logic that searches project by project for the latest file. Is this even possible or do I need to restructure anything?

       

      Best regards

      Manuel

  • Manuel123's avatar
    Manuel123
    Helper I
    5 years ago

    Dear Helpers,

     

    this is a little push. Anyone an idea how to solve this issue?

     

    BR
    Manuel

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Manuel123 ,

    I created a sample pbix file for you, please check whether that is what you want.

    let
    Source = Folder.Files("x:\xx\xx\xx\xxx"),
    #"Added Custom" = Table.AddColumn(Source, "Projects", each Text.BeforeDelimiter([Name],"_")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Date", each Text.From(Text.AfterDelimiter([Name], "_"))),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom1",".csv","",Replacer.ReplaceText,{"Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Date", type date}}),
    #"Grouped Rows" =  Table.Group(#"Changed Type", {"Projects"}, 
                     {{"Latest Record", each Table.FirstN(Table.Sort(_, {{"Date", Order.Descending}}),1), type table [Content=binary, Name=text, Extension=text, Date accessed=nullable datetime, Date modified=nullable datetime, Date created=nullable datetime, Attributes=nullable record, Folder Path=text, Projects=text, Date=nullable date]}}),
    #"Expanded maxdate" = Table.ExpandTableColumn(#"Grouped Rows", "Latest Record", {"Attributes", "Content", "Date accessed", "Date created", "Date modified", "Extension", "Folder Path", "Name"}, {"Attributes", "Content", "Date accessed", "Date created", "Date modified", "Extension", "Folder Path", "Name"})
in
    #"Expanded maxdate"

    Best Regards