cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ianhan13
Helper III
Helper III

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

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

2 ACCEPTED SOLUTIONS
mahoneypat
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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

View solution in original post

6 REPLIES 6
mahoneypat
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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

View solution in original post

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
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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'

ianhan13_1-1609801970689.png

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
Solution Sage
Solution Sage

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)

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors