Forum Discussion
Solved
How to combine multiple excel data using power query
All, I have multiple excel files in single folder which have same form however within headers there is some additional spaces at start or end in few excel sheets of workbook. when I tried to combine those in power query data from all excel is not getting transformed. Getting error for few excel sheets which have additional spaces.
Can any one suggest solution for those excel sheets which have spaces at start or end?
Thank you!
Sumit
In attached file a query that combines all sheets in all workbooks
If this is what you want to do, update the Folder path with yours in sheet 'PARAM' > Refresh the query in sheet 'Query Result'
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 (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 TrimmedHeadersthen 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
refer below snapshot of error message.
test1 file have single worksheet with name as sheet however remaining files have multiple worksheets like 2 or 3 but they all have different names.
Do we need to match worksheet name as well?
Are how things going with your various workbooks & worksheets 'Combination'?
