Forum Discussion
Combining Data From Multiple Worksheets Into one table
Hello,
How can i combine data from multiple worksheets of excel file into one table in PowerBi?
Sample file is attached - Click Here
Thanks
Gaurav
1. After connecting to the spreadsheet, right-click the file name and select Transform Data (don't select each individual sheet):
2. Modify the M code by changing the second argument to "true" (prevents repetition of column headers):
Excel.Workbook(File.Contents("C:\Users\MultiSheetImport.xlsx"), true, true)3. In the Data column, click the Expand icon (double arrow):
4. Uncheck "Use original column name as prefix":
Result:
3 Replies
1. After connecting to the spreadsheet, right-click the file name and select Transform Data (don't select each individual sheet):
2. Modify the M code by changing the second argument to "true" (prevents repetition of column headers):
Excel.Workbook(File.Contents("C:\Users\MultiSheetImport.xlsx"), true, true)3. In the Data column, click the Expand icon (double arrow):
4. Uncheck "Use original column name as prefix":
Result:
Hi,
See this video link -Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks.
Thank you for this! This solution has really made a difference automating a feed into PBI where the source excel file have identical sheets added.
