Forum Discussion

flowgistics's avatar
flowgistics
Frequent Visitor
5 years ago
Solved

Filter column if Source.Name corresponds to Source.Name in last row

Hi everyone, I'm not looking through M-code that well yet and hope that somebody can help me with my problem:   I'm importing .txt files through folder import, but only want the latest report to b...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi flowgistics 

     

    Have been very busy lately. So you have all the files in one folder like this? And you want the archive data file and the latest version of xyz? Are you going to use the Date created or the date in filename to identify the latest version?

    here is one using Date created

    let
    Source = Folder.Files("folderpath"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name],"archive") or [Date created] = List.Max(Source[Date created]))
in
    #"Filtered Rows"

     

flowgistics's avatar
flowgistics
Frequent Visitor
5 years ago

Hi Anonymous 

For better understanding I've created a small example of my handled data:

Source.Name ProductionID ProductionTime ProductionAmount Index
archiveData 111111 01.04.2017 06:35 59500 1
archiveData 111112 01.04.2017 07:20 59700 2
… … … … …
archiveData 112879 01.04.2020 04:50 60300 90013
xyz_20210715 121111 01.04.2020 06:30 60000 90014
xyz_20210715 121112 01.04.2020 06:44 59000 90015
… … … … …
xyz_20210715 131456 15.07.2021 05:10 61500 125627
xyz_20210801 121111 01.04.2020 06:30 60000 125628
xyz_20210801 121112 01.04.2020 06:44 59000 125629
… … … … …
xyz_20210801 141378 01.08.2021 04:57 60200 130235
xyz_20210815 121111 01.04.2020 06:30 60000 130236
xyz_20210815 121112 01.04.2020 06:44 59000 130237
… … … … …
xyz_20210815 151621 15.08.2021 05:16 61000 135365

 

So at the moment the folder import loads archive data rows from 2017 until the morning of the 1st April 2020, as well as reports created twice a month with the current data from the 1st April 2020 until the morning the report was created..

In my model, I want to load archive data as well as the rows of the latest available report which would be "xyz_20210815" at the moment.

Hope that explanation helps?

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi flowgistics 

 

Have been very busy lately. So you have all the files in one folder like this? And you want the archive data file and the latest version of xyz? Are you going to use the Date created or the date in filename to identify the latest version?

here is one using Date created

let
    Source = Folder.Files("folderpath"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name],"archive") or [Date created] = List.Max(Source[Date created]))
in
    #"Filtered Rows"

 

  • flowgistics's avatar
    flowgistics
    Frequent Visitor
    5 years ago

    Hi Anonymous 

    Thanks that works!

    Regarding using the filename instead: can you think of a solution using the current date in order to compare it to the closest date of a filename and then select this file?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi flowgistics 

     

    you mean 20210815 extracted from filename and compare with today? Can't use the Max date...you might get some date in the future?