How to combine multiple excel data using power query

Hi Sumit_Bhokare 

however within headers there is some additional spaces at start or end in few excel sheets

Below is a simulation. Not obvious but Sheet2 headers are: ' abc '; 'def '; ' ghi '

What you need to do, after Promoting Headers on each sheet, is to add a step (with the Advanced Editor) to Trim the header names (https://learn.microsoft.com/en-us/powerquery-m/table-transformcolumnnames). Example with above Sheet1:

let
    Source = Excel.CurrentWorkbook(){[Name="Sheet1"]}[Content],
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    TrimmedHeaders = Table.TransformColumnNames(PromotedHeaders, Text.Trim)
in
    TrimmedHeaders

then you combine your sheets

Re. Getting error for few excel sheets which have additional spaces

Sorry but this means nothing/doesn't help if you don't post the corresponding error message