Uploading the latest version of 6 excel files within a single directory

[ianhan13]

Hi

I am new to Power BI Desktop and know just enought to be dangerous 🙂

I am trying to combine data from 6 separate excel spreadsheets which contain fund performance. 

A new file for each portfolio is created every evening, named with the file name of the portfolio.

I would like to pull the latest version of all 6 files into a Table so I can combine and extract data across the group. 

I have managed a File data upload to get all files in the directory into a Table, but if I filter on  latest file I will of course just get one file. I need to filter for the latest version of all 6 files.

Any help would be very much appreciated 

Ian

[mahoneypat · Accepted solution]

In your screenshot, you want the All Rows option under Operation.  I mocked up your scenario (w/o actual files, just filename) to demonstrate.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvMSTVU0lEy1DfRNzIwMlSK1YEIGoEFjbEJGqEKQrSbogoaY1NpjGJRLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Filename = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Filename", type text}, {"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Filename"}, {{"AllRows", each _, type table [Filename=nullable text, Date=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "WithIndex", each Table.AddIndexColumn([AllRows],"Order",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
    #"Expanded WithIndex" = Table.ExpandTableColumn(#"Removed Columns", "WithIndex", {"Date", "Order"}, {"Date", "Order"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded WithIndex", each ([Order] = 1))
in
    #"Filtered Rows"

Regards,

Pat

[mahoneypat · Accepted solution]

In your screenshot, you want the All Rows option under Operation.  I mocked up your scenario (w/o actual files, just filename) to demonstrate.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvMSTVU0lEy1DfRNzIwMlSK1YEIGoEFjbEJGqEKQrSbogoaY1NpjGJRLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Filename = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Filename", type text}, {"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Filename"}, {{"AllRows", each _, type table [Filename=nullable text, Date=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "WithIndex", each Table.AddIndexColumn([AllRows],"Order",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
    #"Expanded WithIndex" = Table.ExpandTableColumn(#"Removed Columns", "WithIndex", {"Date", "Order"}, {"Date", "Order"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded WithIndex", each ([Order] = 1))
in
    #"Filtered Rows"

Regards,

Pat

[ianhan13 · Accepted solution]

Many thanks for thsi solution works beautifully once I tidied up my file nomenclature.

Now stuck on the post 'Daily additions to historic data file' - if you have any wisewords on this they would be greatly appreciated

Kind regards

Ian

[ianhan13]

Hi Pat

Many thanks for your example code which is super useful.

It doesn't quite work in my scenario because each file name has a subsequent id added by Windows eg (4) , (5) due to the sloppy way the file is created. I think a bit of trimming of file name should sort that problem just trying now. 

Many thanks

Ian

[mahoneypat]

You could do it in a single query as follows:

1. Sort by file modified date descending

2. Group by filename, with Keep All Rows under Advanced in the popup

3. Add a subindex column to the Tables in each row (add an index column with Table.AddIndexColumn() to the column with the tables)

4. Expand the table column, keeping the index and content/binary columns

5. Filter to where the Index column = 1

Regards,

Pat

[ianhan13]

Many thanks Pat Much Appreciated - Sorry but couple of stupid questions coming out of your kind repy

Step 1 Got it

Step 2 On group by Name the advanced pop up gives me a whole bunch of options not quite clear where to  'Keep all roles'

Step3 Standrad add indes from pulldowns gets me

= Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type)

Don't think this is what I need as you say Sub Index to tabel colun this just creates new Indexcolumn

Step 4

Of coure don't have 'index binary' columns since I have messed up Steps 2 and 3

Sorry bear with me I did mention I was at teh damgerous stage but can hopefully get through with a bit of hand holding  🙂

Ian

[StefanoGrimaldi]

for this you will need to make it 6 independent queries and if the end you need them to be in a single table them use a append function in power query, so basically create 6 diferent query one for each file portafolio and if needed them append all 6 queries in a new one (and disable data load for the original 6)