Forum Discussion
Loading data from folder not loading all columns
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?
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"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.
