Power Query | Folder Combine & Transform when columns doesn't exist in all files

[Jerseey]

I'm combining data from multiple .XML files in a specific folder using the Combine & Transform option in Power Query. The files are similar, they all contain some standard information, but the problem is that some of them have a few additional columns.
All files have the following information:

• WBS
• Period
• Amount

However, some of them also have a column for project number and/or project name.

When I try to combine them with the combine and transform option with all columns I get this error message:
"An error occurred in the ‘Transform File’ query. Expression.Error: The column PROJECT' of the table wasn't found."

The reason is that Project column doesn't exist in all YYY.xml file.

However, if I instead had loaded individual XML files and then appended the files together, the combining would have worked as I would just get null in the Project column for the YYY file, which is what I want. However, adding files individually is not an option as there will a lot of files added so I can't add them individually, I need to use the folder option. I need the data in all files, so ignoring files with error is not an option and the excising columns have all the same name if they exist, so different column names is not an issue either. Also, please note that not all rows in the XXX.xml file have a project name and project number, which is fine.

The result I want:

Can anyone help me figure out how to avoid this error?

 

[leishtheman]

Hi, I see there are no responses but did you ever figure this out in the end? I have the same issue and can't find an answer on google.
Thanks

 

[jdellasala]

When importing from a Folder, PQ will generate a Parameter1 parameter, a binary Sample File query, a Transform File Function, and a Transform Sample File query. The Function is automatically generated by the Transform Sample File query. If your finding problems in the final output, the first step is to see if it can be fixed by modifying that query. If that doesn't fix it, change the Sample File binary to another file. By default it will pick the first file in the folder file list by using

= #"Removed Other Columns"{0}[Content]

The {0} is the first row of the previous step. Change it to {1} to try the next file, or determine which row has the problem file, and use that row# - 1 as it shows in the Source step to see how the Transform query handles that.
A simple problem like columns that are not needed shouldn't present a problem. Those columns will be brought in and have null values for files that don't have the column, and those columns can just be removed. Other issues are certainly trickier to fix, but without specifics they're hard to address.