Forum Discussion
Connecting to a shared drive Folder and Refreshing Data Daily
Hello Power BI Family,
I am working on creating a dashboard that will pull data from a centralized folder on a shared drive. Each day a new spreadsheet will be dropped into the folder. The spreadsheets are named by date and I want to give the end user the ability to select the dated spreadsheet to review. The dashboard will have several visualizations but the columns in the spreadsheet will remain the same so dashboard should just refreshed based on the dated spreadsheet that is chosen. I would like to have a simple drop down in the dashboard that will list all of the options available in the folder. From my research the suggestions I have read advise to combine the spreadsheets when connecting to the folder and loading them into Powerbi and to create a Parameter to allow the end user the ability to select the dated spreadsheet they would like to review but this approach is not working for me and I lose all of my column headers when attempting to use the combine the spreadsheets as I am loading it into PowerBi. I have included screenshots of dummy data with similar headers. Any suggestions or alternative ways to approach this would be greatly appreciated.
4 Replies
- lbendlin
Super User
The recommendation is mostly correct. It omitted the need to include the file name in the result, and glazed over the ingestion of the individual files. The "Combine binaries" feature is only a very crude first approximation of what you shoud do. What you should do is write your own combiner function based on Table.AddColumn.
Please provide a couple of sample files , covering all the different formats that you expect.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.Thank you lbedlin. I have a dropbox link with a copy of the files https://www.dropbox.com/scl/fo/47wl9mmbidrvom03j2j1i/h?rlkey=mpgofpbp5hxb3cxkzf8wxzf8o&dl=0
I am looking for a simple date picker in the dashboard that will mimic the name of each file that is saved. Each file that is saved is the folder will be named by the date.
- lbendlin
Those files have all the same structure. Didn't you say they may be different?
Here is the Power Query code to combine all files in a certain folder that end with "Ticket.xlsx"
let Source = Folder.Files("C:\Users\xxx\Downloads"), #"Filtered Rows" = Table.SelectRows(Source, each Text.EndsWith([Name], "Ticket.xlsx")), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Data", each Table.PromoteHeaders(Excel.Workbook([Content]){[Item="Sheet1",Kind="Sheet"]}[Data], [PromoteAllScalars=true])), #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Name", "Data"}), #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Data", {"Created", "Ticket ID", "Reporter", "Description", "Status", "Notes", "Resolved Date", "Level"}, {"Created", "Ticket ID", "Reporter", "Description", "Status", "Notes", "Resolved Date", "Level"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Data",{{"Created", type datetime}, {"Ticket ID", type text}, {"Reporter", type text}, {"Description", type text}, {"Status", type text}, {"Notes", type text}, {"Resolved Date", type date}, {"Level", type text}}) in #"Changed Type"Then in Power BI you can have your slicer where users can select data for an individual file
They will have the same structure just different data will be added to the new files that are generated daily and are added to the folder. Thank you soo much for your help with this project!
