none
Power Query to import Only Headers RRS feed

  • Question

  • Hi All,

    I have a folder in my desktop contains ~50 excel workbook contains raw data.

    And there is another workbook where in table 1 column A contains the headers names from range A1 to A10.

    1) Now I want to import only headers from all those workbook and compare if any of the header is missing in any files. if yes than I need that workbook name.

    2) I want to know if power query can do this or not ? Suppose 1 of imported file have date column and in that columns few cells contains Today() or now() formula. So I want to know if I can apply filter in Power Query edit mode to select those entries ?

    Thanks in advance for your valuable feedback.


    Rajender

    Monday, April 29, 2019 4:16 PM

Answers

  • You can use Table.ColumnNames to get the field Names or Column Names of your tables in Power Query, so answering your questions, yes - this is possible. How it needs to be done relies heavily on how your data looks like. If your data is inside of Excel Tables, then its fairly simply to target those objects, but if they're inside of Sheets as named ranges or "un named" ranges, then you'll need to find your data and promote the headers from the first row of data.

    In regards to your specific 2nd question, Power Query will not know what formula is being used at your cell level, so you won't know exactly if the TODAY(), NOW(), VLOOKUP(), LEN(), or any other type of function is being used in a cell from that Excel Workbook. At least not with the native Excel Connector. 

    Tuesday, April 30, 2019 11:58 AM

All replies

  • You can use Table.ColumnNames to get the field Names or Column Names of your tables in Power Query, so answering your questions, yes - this is possible. How it needs to be done relies heavily on how your data looks like. If your data is inside of Excel Tables, then its fairly simply to target those objects, but if they're inside of Sheets as named ranges or "un named" ranges, then you'll need to find your data and promote the headers from the first row of data.

    In regards to your specific 2nd question, Power Query will not know what formula is being used at your cell level, so you won't know exactly if the TODAY(), NOW(), VLOOKUP(), LEN(), or any other type of function is being used in a cell from that Excel Workbook. At least not with the native Excel Connector. 

    Tuesday, April 30, 2019 11:58 AM
  • Hi Miguel,

    Thanks a ton, I am able to solve my issue using Table.ColumnNames  function. I need more advice from you. 

    Right now to get my output I created 5 tables. I know there will be more efficient way as well. if you can suggest me for the same as well ?

    1) first I Imported all the files from Folder and used Table.columnNames. I deleted all the columns except file name and output of Table.columnames.

    2) In second step I imported the new table which contains the Header list which I needs to check in all the files and added custom column mentioning 1 against all headers.

    3) In third step I used the same query mentioned in Point 1 and deleted all the columns except file name and added new custom column and printed 1 against all file names.

    4) In 4th step I merged table of point 2 & 3 as new table to get all the possible combination against all files with headers.

    5) Here I merged table of Point 1 & Point 4 as Right Anti only to get missing header names.

    6) Here I merged table of Point 1 & Point 4 again with Left Anti only to get any New Column added

    Thanks in advance for your valuable suggestion, How I can improve my above mentioned logic.

     


    Rajender

    Wednesday, May 1, 2019 5:38 PM