none
Power query missed columns when import data from xlsx files RRS feed

  • Question

  • Hi team,

    Today I just had an issue like this.

    I tried to import data from a folder which contains a few excel files. Each file contains different amount of columns. One of these files got 46 columns. When I import the files from the folder, some of the columns in that file missed in the query for unknown reason.

    However, when I import the data from that single file, none of the 46 columns were missed from that file.

    I am using Excel 2013 now.

    Could you please tell me why did that happen and how can I fix that problem?

    Cheers,

    Tom Sun


    • Edited by Tom J Sun Wednesday, January 4, 2017 3:11 AM Edit
    Wednesday, January 4, 2017 3:04 AM

Answers

  • Hi Tom,

    this could happen, if you import objects of type table or named range (instead of sheet) and those haven't been adjusted to include new columns.

    If that's not the reason, please paste you query code.


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Tom J Sun Wednesday, January 4, 2017 11:30 AM
    Wednesday, January 4, 2017 6:40 AM
    Moderator

All replies

  • Hi Tom,

    this could happen, if you import objects of type table or named range (instead of sheet) and those haven't been adjusted to include new columns.

    If that's not the reason, please paste you query code.


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Tom J Sun Wednesday, January 4, 2017 11:30 AM
    Wednesday, January 4, 2017 6:40 AM
    Moderator
  • Hi Imke,

    Thanks for the advice.

    I did check the code and find that the issue comes exactly as you mentioned.

    let
        Source = Folder.Files("C:\Users\Xiaob\Desktop\Dash\ASP"),
        #"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Data"}, {"Data"}),
        #"Expanded Data" = Table.ExpandTableColumn(#"Expanded Custom", "Data", {"Column1", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column2", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column3", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Column1", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column2", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column3", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}),
        #"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([Column1] = "Category" or [Column1] = "CELLULAR TELEPHONES" or [Column1] = "TABLET")),
        #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1)

    in

    #"Added Index"

    As it can be seen that the max column number listed in this query is 36. (This is the max column number when I first import data from this folder). After that I add new files into this folder and those files have more than 36 columns.

    In order to resolve this issue, is there any way to dynamically reference the column numbers?

    In addition, when I try to expand the tables to get the data, the columns are not the in the same order when they are in the file (Marked in black colour as well).

    Is there any way can sort the column from column1 to maximum number?

    Cheers,

    Tom Sun

     

     


    • Edited by Tom J Sun Wednesday, January 4, 2017 11:29 AM edit
    Wednesday, January 4, 2017 11:29 AM
  • Yes, you can use this statement instead:

        #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", List.Union(List.Transform(#"Removed Other Columns"[Custom], each Table.ColumnNames(_))), List.Union(List.Transform(#"Removed Other Columns"[Custom], each Table.ColumnNames(_))))

    This will dynamically expand all columns of all files in the folder. Not sure about the sort-order though, as it could theoretically be different in the files (Pls let me know how this works for your data).


    Imke Feldmann TheBIccountant.com

    Wednesday, January 4, 2017 11:49 AM
    Moderator
  • Hi Imke,

    Thanks for much for the code.

    I tried it and worked perfectly.

    Cheers,

    Tom Sun

    Wednesday, January 4, 2017 12:35 PM
  • Hi Imke,

    Today I tried this code in a query.

    It does work while it seems that this code needs much longer time to finish working comparing with normal expand column.

    Could you please tell me how this happened?

    Cheers,

    Tom Sun

    Thursday, January 12, 2017 12:49 AM
  • Yes, the evaluation of the expression returning the column names might take longer here that using the hardcoded values. Try buffering the table which might get queries multiple times like this:

        #"Removed Other Columns" =Table.Buffer( Table.SelectColumns(#"Added Custom",{"Custom"})),


    Imke Feldmann TheBIccountant.com

    Thursday, January 12, 2017 5:01 AM
    Moderator
  • Hi Imke,

    Thanks for the information.

    In regards to buffer function, no matter in list or table, my understanding for this is that it transfers the data of a list or table into memory and can not be edited or changed during evaluation process to achieve a higher speed.

    Is this correct?

    However, if this is true, during the expand process, the format of the table has been changed. Does this effect the expand process?

    Cheers,

    Tom Sun


    • Edited by Tom J Sun Thursday, January 12, 2017 10:02 PM Edit
    Thursday, January 12, 2017 10:01 PM
  • Hi Tom,

    unfortunately I don't know how the Buffer-functions actually works.

    It's just my experience that they sometimes speed up the process. Trial- & error-ing my path along ;-)


    Imke Feldmann TheBIccountant.com

    Friday, January 13, 2017 5:30 AM
    Moderator