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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joeality
Frequent Visitor

Combine Multiple spreadsheets of Data into a Single tall table

I have twelve spreadsheets representing montly data for the past twelve months. If possible I'd like to concatenate the tables into a single tall table and add a month signifier to each row based on the spreadsheet they originated from. 

1 ACCEPTED SOLUTION

Hi @joeality

What you should do is work in Power BI Desktop and follow the steps below; 
- Create a new query and connect to the root folder where all your files are stored. As result you get a list of all files in the folder, for example a sharepoint folder. 
- Now you will see the Content column where the value shows binary for every row. This represents the content of each file. In the columnheader you see a little icon. Click this icon. 
- Now all files will be combined in one new big table. Power BI will create a function which loops over all files in the folder. 

Be warned! Every file needs to be in the same structure and same extension. 

As an example, watch this video. This is Power Query in Excel, but works exactly the same in Power BI. In the video he is creating a custom column with the Excel.Workbook() function. This is not necessary if you just click the small icon in the Content column. Otherwise you won't create a Power BI function to loop over all the files. 
To create a filter in your report to switch between the files, you can keep the Name column which represents the file name or every other column you want. This column can be used in your report as a filter / slicer to dynamicly switch between the files. 



- Marc 

ps.  Take a look at the Power BI Cheat Sheet which you can download for free here

View solution in original post

1 REPLY 1

Hi @joeality

What you should do is work in Power BI Desktop and follow the steps below; 
- Create a new query and connect to the root folder where all your files are stored. As result you get a list of all files in the folder, for example a sharepoint folder. 
- Now you will see the Content column where the value shows binary for every row. This represents the content of each file. In the columnheader you see a little icon. Click this icon. 
- Now all files will be combined in one new big table. Power BI will create a function which loops over all files in the folder. 

Be warned! Every file needs to be in the same structure and same extension. 

As an example, watch this video. This is Power Query in Excel, but works exactly the same in Power BI. In the video he is creating a custom column with the Excel.Workbook() function. This is not necessary if you just click the small icon in the Content column. Otherwise you won't create a Power BI function to loop over all the files. 
To create a filter in your report to switch between the files, you can keep the Name column which represents the file name or every other column you want. This column can be used in your report as a filter / slicer to dynamicly switch between the files. 



- Marc 

ps.  Take a look at the Power BI Cheat Sheet which you can download for free here

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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