Loading data from folder not loading all columns

[bdd9]

Loading data from folder not loading all columns, I am not getting all 49 columns from my .xlsx files. 

I am only getting the 1st 39 columns. I believe it has something to do with the just getting the columns in the data preview.

The files & column names are all identical. I get the same error if I load 1 file or multiple.

I have searched and searched for a solution but haven't found one. any help would be greatly appreciated.

[Salmancert]

Hi

I have found the problem, the issue is with the sample file being used to reference the load files. in your sameple file the number of columns were less than your actual file. You can change this by going to power query and select the sample file, you will see the code as follow in advanced editor

let

    Source = (Parameter1 as binary) => let

    Source = Parameter1,

    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(3) > * > TR > :nth-child(1)"}, {"Column2", "TABLE:nth-child(3) > * > TR > :nth-child(2)"}, {"Column3", "TABLE:nth-child(3) > * > TR > :nth-child(3)"}, {"Column4", "TABLE:nth-child(3) > * > TR > :nth-child(4)"}, {"Column5", "TABLE:nth-child(3) > * > TR > :nth-child(5)"}, {"Column6", "TABLE:nth-child(3) > * > TR > :nth-child(6)"}, {"Column7", "TABLE:nth-child(3) > * > TR > :nth-child(7)"}, {"Column8", "TABLE:nth-child(3) > * > TR > :nth-child(8)"}, {"Column9", "TABLE:nth-child(3) > * > TR > :nth-child(9)"}, {"Column10", "TABLE:nth-child(3) > * > TR > :nth-child(10)"}, {"Column11", "TABLE:nth-child(3) > * > TR > :nth-child(11)"}, {"Column12", "TABLE:nth-child(3) > * > TR > :nth-child(12)"}, {"Column13", "TABLE:nth-child(3) > * > TR > :nth-child(13)"}, {"Column14", "TABLE:nth-child(3) > * > TR > :nth-child(14)"}, {"Column15", "TABLE:nth-child(3) > * > TR > :nth-child(15)"}}, [RowSelector="TABLE:nth-child(3) > * > TR"])

in

    #"Extracted Table From Html"

in

    Source

in my example the columns are limited to 15. to increase it add the code for remaining columns as 

let
Source = (Parameter1 as binary) => let
Source = Parameter1,
#"Extracted Table From Html" = Html.Table(Source, {
{"Column1", "TABLE:nth-child(3) > * > TR > :nth-child(1)"},
{"Column2", "TABLE:nth-child(3) > * > TR > :nth-child(2)"},
{"Column3", "TABLE:nth-child(3) > * > TR > :nth-child(3)"},
{"Column4", "TABLE:nth-child(3) > * > TR > :nth-child(4)"},
{"Column5", "TABLE:nth-child(3) > * > TR > :nth-child(5)"},
{"Column6", "TABLE:nth-child(3) > * > TR > :nth-child(6)"},
{"Column7", "TABLE:nth-child(3) > * > TR > :nth-child(7)"},
{"Column8", "TABLE:nth-child(3) > * > TR > :nth-child(8)"},
{"Column9", "TABLE:nth-child(3) > * > TR > :nth-child(9)"},
{"Column10", "TABLE:nth-child(3) > * > TR > :nth-child(10)"},
{"Column11", "TABLE:nth-child(3) > * > TR > :nth-child(11)"},
{"Column12", "TABLE:nth-child(3) > * > TR > :nth-child(12)"},
{"Column13", "TABLE:nth-child(3) > * > TR > :nth-child(13)"},
{"Column14", "TABLE:nth-child(3) > * > TR > :nth-child(14)"},
{"Column15", "TABLE:nth-child(3) > * > TR > :nth-child(15)"},
{"Column16", "TABLE:nth-child(3) > * > TR > :nth-child(16)"},
{"Column17", "TABLE:nth-child(3) > * > TR > :nth-child(17)"},
{"Column18", "TABLE:nth-child(3) > * > TR > :nth-child(18)"},
{"Column19", "TABLE:nth-child(3) > * > TR > :nth-child(19)"},
{"Column20", "TABLE:nth-child(3) > * > TR > :nth-child(20)"},
{"Column21", "TABLE:nth-child(3) > * > TR > :nth-child(21)"},
{"Column22", "TABLE:nth-child(3) > * > TR > :nth-child(22)"},
{"Column23", "TABLE:nth-child(3) > * > TR > :nth-child(23)"},
{"Column24", "TABLE:nth-child(3) > * > TR > :nth-child(24)"},
{"Column25", "TABLE:nth-child(3) > * > TR > :nth-child(25)"},
{"Column26", "TABLE:nth-child(3) > * > TR > :nth-child(26)"}
}, [RowSelector="TABLE:nth-child(3) > * > TR"])
in
#"Extracted Table From Html"
in
Source

in my case I have extended it to 26 columns. this will solve the column nuber limitation.

hope this solves your problem as it has solved mine

[nickvanmaele]

Update: I have tested by creating a completely new Power BI model. 

I then created a custom function to process the files but the error remained when using the first file as the sample file. Starting over and taking each file in turn as the sample file, the error eventually disappeared when using the third file in the folder. Why this is so, is still an enigma to me, since all first three files in that folder have identical layouts, column titles, and data formats in each column. 

[nickvanmaele]

I have the same problem.

I have a few Excel files (.xlsx) stored in a SharePoint folder.

All files have data on Sheet1.

The data are not in Tables but in normal ranges.

On each Sheet 1:

• the first row has a report title in cell A1 but is empty as of B1, C1, ...
• the second row has the column names
• the third and following rows having the data
• the data consist of 49 columns
• all files have identical column names for all 49 columns in Excel cells A2 to AW2

When I connect to the folder, and then drill into a binary for a single file, 

i.e., by executing #"Import Excel"{[Item="Sheet1",Kind="Sheet"]}[Data]

only 33 columns of data are read and shown in Power Query,

with the 34th column showing all values as null,

and no further columns appearing. 

However, from the screenshot below, it is clear that this column does contain text values in the Excel file. 

How do I get Power Query to read column 34 correctly and also read columns 35 to 49?

Any help would be much appreciated. 

[Greg_Deckler]

@bdd9 Are you sure it is an xlsx file and not a csv file? I was unable to replicate the behavior using an xlsx file even if I had four columns with data and then a completely empty column and then 110 more columns that didn't have values until after about 1,500 rows (further than data preview). CSV's have an optional parameter where you can specify the number of columns and sometimes you need to get rid of that if it incorrectly comes up with the wrong number of columns. Excel.Workbook has no such option. Are you getting your data from a Sheet or a Table?

[bdd9]

2 other things I need to accomplish:

1) get the file name as a column in the output data (https://www.youtube.com/watch?v=qdcAoZU8B8Q&feature=youtu.be)

2) be able to handle data columns in future exports. i.e. I currenly need 49 columns but I could have new columns added. (https://www.youtube.com/watch?v=UY3hIV-THcg&t=183s)

But I haven't got to this point.

I found documentation on youtube to handle both items. that is why I added the custom column and filtered to data.

I still don't get all the columns if I just click the double arrow on content after the "Source" step and let power query user the 1st file as a sample file.

[bdd9]

Yes. both are .xlsx files. And I'm pretty sure data is coming from sheet. There are no tables in the files.

Here is my code from the advanced editor:

let
Source = Folder.Files("C:\Users\braden\OneDrive\Documents\Football\AHS\Football Staff\Exports\AHS"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Data"}, {"Custom.Data"})
in
#"Expanded Custom"