Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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
Microsoft Employee
Microsoft Employee

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

Anonymous
Not applicable

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
Microsoft Employee
Microsoft Employee

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


Anonymous
Not applicable

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

Anonymous
Not applicable

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
Microsoft Employee
Microsoft Employee

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


Anonymous
Not applicable

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
Resident Rockstar
Resident Rockstar

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)





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.