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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

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


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

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


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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.