Combining Data From Multiple Worksheets Into one table

[gaurav-narchal]

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

[merryone]

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.

[Ashish_Mathur]

Hi,

See this video link  -Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks.

[DataInsights · Accepted solution]

@gaurav-narchal,

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: