Hi Ricardo

Not sure I follow everything, but when you connect to Files from a Folder, PQ creates some Helper Queries including a "Transform File" function, by default and a final query that contains the Source, some filtering before it invokes the helper function and expands the data. The expand step should look like this:

#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))

The red-bolded part expands all files based on the column names it picked up from the Sample file (Column1, Column2 etc.). That usually is the first file in the folder. If you now add another file to the folder which has extra columns, these will not be imported. You have to remove the expand step and replace it with a Table.Combine statement like this:

Combine = Table.Combine (#"Removed Other Columns1"[Transform File])

This will combine the nested tables, irrespective of the number of used columns in them. Columns with same names will append neatly, new columns will get their own column in the combined table. 

If all of this makes no sense, I probably misunderstood your question completely.

Riny

Hi Riny,

I'm trying to explaining better my issue. I don't have any helper queries.

My source is:  Source = Folder.Files("Path")

I attach the begining of the M Code of the query with the problem. The last command is where I have the problem.

You can also find couple of Excel files I'm loading in my query.

Let me kown if now is a little more clear.

Thansk a lot,

Riccardo

Difficult to diagnose. I note that your files have mostly Italian column names, but some have English for the same type of data. That would be difficult to combine. And I don't really understand how you can not have Helper queries when you use the File, From Folder connector.

Can you upload the Excel file with the query? In stead of a Word document, that is. 

Hi Riccardo,

Riny is expecting you to have used the technique shown in lesson 2.05 of the Power Query course. Have you tried that and then applied Riny's soltuion?

Mynda

Hi,

I'm not using the technique use in lesson 2.05, I'm not expanding the "binary" colums and therefore I don't have any Helper query.

I'm using this command: Table.AddColumn(#"Renamed Columns", "Tables", each Excel.Workbook([Content],false))

I attach the Excel file with the query and 3 example files to be added in a folder. (Choose a folder name and change the source path)

If you copy one the the attached files and change a "name surname" in a a column, the value in the column will not be "expanded"

Thanks

Riccardo 

Hi,

I added a "regular" From Folder query to your file. It connects to a Folder called TEST in my own set-up. Change the source to make it point to your own. Personally, I prefer a set-up with the questions and evaluations in rows and the clients/projects in columns. That make the end-result easier to review.

Please have a look at the attached file and see if this could work for you. The result of my query is loaded to the TEST sheet.

Hi Riny,

thanks for your help.
I'm not able to download your Excel file, I get an error "Sorry, the page you are looking for can't be found."
I just click on the file name. 

Is there a different way of dowloading the files?

Ciao
Riccardo

Strange. Must have done something wrong.

Trying again.

Ciao Riny,

now the download was working and I was able to test your query proposal.

The query works perfectly but I have the same problem as before.

If I add a new file in the folder, and press "Refresh All", I get only the data with the same column headers.

I have 4 files in the "Sheet1" but I get the value only for the column headers the are the same in the all the files.

In the "Test" sheet there is no new value.

I attach the 4 file I tested.

Thanks for your help.

Riccardo

I see. The individual files are just too inconsistent, I'm afraid. I believe you should put some effort into developing a template that can be used for all your clients/projects. Reserve columns for extra PM's and others assigned to a project. If you need to mix languages, make sure that, for instance  "Posta electronica" and "Email" are always in the fourth column.

And further on, the first three files referenced to "PM (name)", though the fourth to "PM company (name)". Avoid these inconsistencies if you want to extract "PM name". The query I created based on the first three files simply doesn't catch that.