Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe 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
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
To learn more about Power BI, follow me on Twitter or subscribe 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
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
To learn more about Power BI, follow me on Twitter or subscribe 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'
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
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)
Proud to be a Super User!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
83 | |
82 | |
65 | |
49 |
User | Count |
---|---|
135 | |
111 | |
100 | |
65 | |
62 |