Forum Discussion

bdd9's avatar
bdd9
Frequent Visitor
3 years ago

Loading data from folder not loading all columns

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 gettin...
Need Help
Salmancert's avatar
Salmancert
New Member
1 year ago

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