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 21, 2023

Sumit_Bhokare 

Are how things going with your various workbooks & worksheets 'Combination'?

Sumit_Bhokare's avatar
Sumit_Bhokare
Brass Contributor
Dec 28, 2023

Lorenzo

 

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?

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 28, 2023

    Sumit_Bhokare 

     

    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
    ),

     

    • Sumit_Bhokare's avatar
      Sumit_Bhokare
      Brass Contributor
      Dec 28, 2023

      Lorenzo 

       

      working on different excel which have multiple queries, so do I need to change anything.

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Dec 28, 2023

        Sumit_Bhokare 

        working on different excel which have multiple queries, so do I need to change anything

        Maybe/I don't know. I don't have in front of me what you have. Share the workbook you're working with...

Resources