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"

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi flowgistics 

 

I don't seem to understand your question

Two questions:

  1. how would you realize a parameter to be able to only load the latest input data rows into the model? Do you mean the rows in your latest txt file or you mean the latest version as of your August in the sample?
  2. is it possible to handle the archive data file with folder import as well or is that too complicated to realize and go for a standard file import for this file instead? What do you mean archive data file?

For example, you read a folder, you can use the columns Date created or others to identify the latest version OR extract the date in your filename as shown in your sample to compare and filter to the files you need

 

 

 

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?