Loading specific files into PowerBI

[UsePowerBI]

Hello

Can you tell me please how to load the newest file from a folder C:\Directory to PowerBI that have filename File1*.xlsx, the newest File2*.xls etc?

Thanks!

[v-jingzhang · Accepted solution]

Hi @UsePowerBI 

Try these codes:

let
    Source = Folder.Files("C:\Users\YourFolder"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "A") or Text.StartsWith([Name], "B")),
    #"Inserted First Characters" = Table.AddColumn(#"Filtered Rows", "First Characters", each Text.Start([Name], 1), type text),
    #"Grouped Rows" = Table.Group(#"Inserted First Characters", {"First Characters"}, {{"Newest Date Created", each List.Max([Date created]), type nullable datetime}, {"GroupTable", each _, 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, First Characters=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.SelectRows([GroupTable], let latest = [Newest Date Created] in each [Date created] = latest)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "First Characters"}, {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "First Characters"})
in
    #"Expanded Custom"

Insert a column to help group by. You can modify this step per your need. 

Remove columns except for Custom column and expand it to get the filtered result.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[HotChilli]

Are you writing this yourself? You can do it with the interface.  You probably won't have to write any lines.

Once you have it correct for one file, create a new blank query.  copy the code and change the filename requirements.

You probably want Text.StartsWith instead of Text.Contains

[UsePowerBI]

@HotChilli  thanks, I am trying to figure out what should be the acceptable format. So far, I have managed to find the latest file named A*.xlsx.

I need to include in my selection the latest file named B*.xlsx in the folder.

Is there a neat way so that I don't have to write many lines?

let
Source = Folder.Files("C:\Users\"),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "A") and [Extension] = ".xlsx"),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", let latest = List.Max(#"Filtered Rows"[Date created]) in each [Date created] = latest)
in
#"Filtered Rows1"

[HotChilli]

You should be able to do this using the 'Folder' source in Power Query.

You can get a list of files and apply filters for name, sort based on various dates and use Keep Top rows .

This will produce a generic script that will pull in the appropriate file when a refresh is done.

Depending on the files you have to load you might have to use a separate query for each of your named files, File1, File 2