Error when loading from a folder

[randyrettig]

Hi all,

I am trying to load multiple .json files from one folder.  All of the files have the same structure except, for one column.  Some files have an extra column called proof.proof_email_array_add.  Because some files have this column and some don't, Power BI is only loading files that have that column, and as soon as it hits a file that doesn't have that column, it stops loading files. 

I should have 40 files, but only the first 3 files have that extra column so those get loaded, and the 4th file (does not have the extra column) comes up as error and then no more files get loaded after that. 

I've tried several things, but I can't seem to be able to figure out how to load these other files.  Can anyone help?

[Fowmy]

@randyrettig 

The file names are hard coded when you first use connect from folder. Delete the query and recreate it.

[Greg_Deckler]

@randyrettig So, best guess on this is that in your sample file you have proof.proof_email_array_add and likely have a step to remove that column. What you need to do is to change that step to instead of removing the column, select the columns you want and remove other columns. So a Table.SelectColumns versus a Table.RemoveColumns. But, regardless, you need to remove any explicit reference to that column in the code. Otherwise, on files that do not have that column you will receive the error you are getting.

[randyrettig]

Thanks for the reply @Greg_Deckler .

I haven't altered the sample file at all.  I have tried that though, and the error persists.  I've even loaded one file as a sample, selected the files I want, and then turn that file into a fuction.  Then I load the folder of files, and don't combine, just transform, delete all columns except file name, then add a column to invoke a function based off of the sample that I altered, and the error persists even then.  Using this method I will get all 40 of the files to load, but the 10 or so that have the extra column will load as "error", and I can't pull any data out of them.

[Greg_Deckler · Accepted solution]

@randyrettig You'll need to open your queries in Advanced Editor, copy and paste them into Notepad and search for the offending column name. It's in there somewhere.

[randyrettig]

@Greg_Deckler @Fowmy Thank you both for your help!  Going into advanced editor and manually removing worked perfectly.