none
Combine three excel file in power query RRS feed

  • Question

  • Hi ,

    i am trying to combine three excel file,one file contains header and other file does not contains header details,

    I have tried two options as listed below:

    1.I have selected example file as first file ,i am getting data from only from first file not from other file(2&3).

    2.I have selected example file as second file ,now i am getting data from all files but the header details are coming in 5th row in combine table.(this header row is not constant ,row number will be vary if i have insert more date in excel file)

    Question:
    1. how to combine this three excel file ?
    or is it possible to combine that one sheet with header and other sheet without header ?
    Excel file and data listed below:

    Excel file1 :

    ID  NAME                   

    1    Kumar                   

    2    Ramu

    Excel file2:

    3      pandian

    4      deepak

    Excel file3:

    5     somu

    6    kevin

    Please advice me how to handle this?

    Thanks and regards,

    Pandian Selvaraj


    Saturday, May 23, 2020 5:09 AM

Answers

  • Hi Pandian

    Your scenario consists of exceptions (not all sheet formatted the same). Consequently and as you've experienced you cannot a standardized process/functionality as you did

    Your list of file names & sheet names won't help to help you :(

    Easiest way to do what you want - if you can - is to format Kumar, Ramu, pandian...kevin as Tables in which case, wherever these Tables seat in the sheets you'll get their content and that content only

    If you can't format as Tables you must find a consistent pattern to detect where (row/column) your data start on each sheet and do something like what's been done on Consolidate multiple sheet data from relative file path reference and relative worksheets where a custom function (fxTableFromSiteIdPostion) searches for the consistent "Site ID" string that is always expected in the first 2 columns between row 1 and 10 - corresponding workbook avail. here

    Hope this makes sense & helps

    Saturday, May 23, 2020 9:16 AM

All replies

  • Hi Pandian

    Your scenario consists of exceptions (not all sheet formatted the same). Consequently and as you've experienced you cannot a standardized process/functionality as you did

    Your list of file names & sheet names won't help to help you :(

    Easiest way to do what you want - if you can - is to format Kumar, Ramu, pandian...kevin as Tables in which case, wherever these Tables seat in the sheets you'll get their content and that content only

    If you can't format as Tables you must find a consistent pattern to detect where (row/column) your data start on each sheet and do something like what's been done on Consolidate multiple sheet data from relative file path reference and relative worksheets where a custom function (fxTableFromSiteIdPostion) searches for the consistent "Site ID" string that is always expected in the first 2 columns between row 1 and 10 - corresponding workbook avail. here

    Hope this makes sense & helps

    Saturday, May 23, 2020 9:16 AM
  • Hi Lz,

    Thanks for your reply..

    Data source is Share point and asked share point team to make header in all sheet or make as table.


    Saturday, May 23, 2020 1:21 PM