Forum Discussion

Sumit_Bhokare's avatar
Sumit_Bhokare
Brass Contributor
Dec 15, 2023
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 ...
BI & Data Analysis
Lorenzo's avatar
Lorenzo
Silver Contributor
Dec 15, 2023

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

Resources