Forum Discussion
How to combine multiple excel data using power query
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 (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
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'?
can you please let me know how to apply same step to multiple queries? I have almost 26 queries and need to trim headers of those so instead of doing it one by one can we do it once for all 26 queries?
In the sample workbook I provided you the query trim the headers of all sheets in all workbooks, after promoting the headers:
TrimmedHeaders = Table.AddColumn(ExpandedSheetsAsTables, "CLEAN_HEADERS", each Table.TransformColumnNames( Table.PromoteHeaders([SHEET_TABLE], [PromoteAllScalars=true]), Text.Trim ), type table ),
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'
As I understand your files don't have the same number of sheets, though you want to combine them all, correct?
Assuming I understood and top of my head the Get & Transform > From File > From folder wizard won't work in such scenario. A custom query is required or you first combine, file by file, all worksheets as one within each workbook where you have > 1 worksheet
If you're not too advanced with Power Query IMHO the 2nd option is the easiest one. Though, you must pay attention to rename the combined sheet (within each workbook) so they have the same name in ALL your workbooks (I guess that was your question)
