cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Load data using Folder option - value based file

Hi, 

 

I am fetching latest file from my workstation using "Folder" option and sorting files using latest timestamp. 

 

Now I want to fetch two files with latest timestamp but files will be having two different names. 

client-keyword 20171121      22/11/2017 14:21

client-adgroup20171121       22/11/2017 14:22   

client-keyword20171121      22/11/2017 10:21

 

 

I am able to fetch 2nd file but now I want to create 2 dashboards one for keyword and one for adgroup. How will I choose files based on file name but with latest timestamp.

 

In short I want to fetch :

client-keyword 20171121      22/11/2017 14:21

client-adgroup20171121       22/11/2017 14:22   

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Load data using Folder option - value based file

Thanks, But I have found another solution of adding Text.contains() in M code which will give me the list of desired files and then sort the data and keep top row.

4 REPLIES 4
Super User
Super User

Re: Load data using Folder option - value based file

If I did this right, this will get you the first 2 latest files:

 

let
    Source = Folder.Files("c:\temp\powerbi\csvs"),
//Latest File
    #"Filtered Rows Latest" = Table.SelectRows(Source, let latest = List.Max(Source[Date accessed]) in each [Date accessed] = latest)

//Second Latest File
    #"Filtered Rows1" = Table.SelectRows(Source, let latest = List.Max(Source[Date accessed]) in each [Date accessed] <> latest),
    #"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", let latest = List.Max(#"Filtered Rows1"[Date accessed]) in each [Date accessed] = latest)

    Append = Table.Combine({#"Filtered Rows Latest", #"Filtered Rows"})
in
    Append

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Re: Load data using Folder option - value based file

Hey smoupre,

 

It is giving me 2 latest file but my problem is I want my files to be picked up by their names. 

if Filename contains Adgroup and is of latest timestamp

and if filename contains keyword and it is also of latest timestamp for "Keyword" value

Community Support Team
Community Support Team

Re: Load data using Folder option - value based file

@Shruti_Goyal94,

 

You may add two calculated columns.

group =
SWITCH (
    TRUE (),
    SEARCH ( "keyword", Table1[name],, 0 ) > 0, "keyword",
    SEARCH ( "adgroup", Table1[name],, 0 ) > 0, "adgroup"
)
rank =
RANKX (
    FILTER ( Table1, Table1[group] = EARLIER ( Table1[group] ) ),
    Table1[timestamp],
    ,
    DESC,
    DENSE
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Load data using Folder option - value based file

Thanks, But I have found another solution of adding Text.contains() in M code which will give me the list of desired files and then sort the data and keep top row.