Forum Discussion
Solved
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:
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: