Hi
I have created a query that pulls dato from Excel workbooks in a folder (using the "from folder" option). The source files contain salary data, one file for each month. After expanding the data column in the query, I unpivot the columns containing data for each employee. My problem is that when I insert a column in the source file (e.g. a new employee), this column is not included in the query. I can see in the step "expanded data" that the columns seem to be "hard coded" which I guess is the reason for the missing update. Can anybody explain to me how to fix this - obviously, I need to add columns from time to time when new people are hired. I have attaced a sample file.
KR Marianne
Hi again
I see now that in my sample file, I can select the new added column in the step "expanded data" and this makes the query update correctly. However, in my "real" file, the added column is not in the list. Any ideas as to what I did wrong in my "real" file?
Hi Marianne,
Is the data in your new file in an Excel Table? In step 5 #Filtered Rows you filter out rows that don't contain a Table. If your new data isn't in a table then it won't be included.
Probably best if you share the query that isn't working. Or try to compare the queries to see where there is a difference.
Mynda
Hi again
Thank you for your reply. I have made some more testing and with the help of a clever colleagues found out what happens:
I have 3 files with salary data named October, November and December which I combine in a query "from folder". When I insert a column with a new employee (column name AAA) in the November source file, go to the query, refresh and click on the step "Expand Column" i cannot see the new column AAA (it is not in the list). On the other hand, when I insert a column in the December file (column name BBB), and do the same as before, I am able to select the new column BBB. It seems that Excel always takes the column names from the first file in the list from the starting step "source" - the list of source files is in my case sorted alphabetically which makes "December" the first file.
So, now I understand why sometimes I could insert columns and sometimes not :-). But how can I fix the problem? The situation is that I want to import my salary data each month, and obviously the number of columns in the source will change, often from month to month as some employees leave and others are hired. I still need to keep the data for previous employees for the months they were employed, and I need to be able to update the query with new people. Perhaps the "from folder" is not the right solution? Any ideas on what else to do?
Thanks again
Marianne
Hi Marianne,
When you import data the first step Power Query often applies is #Changed Type. It tries to identify the type of data you have in each column and apply a data type for you.
In doing this it lists every column name. The solution is to remove this step from your query so that it simply imports all columns.
Then later on in your transforming you can apply the data types yourself once you've unpivoted the employee names from separate columns into one single column. When you do this Unpivot make sure you choose the first 6 columns and then select 'Unpivot Other Columns', as opposed to choosing the employee columns and choosing 'Unpivot Columns'. This way any new columns added for employees will automatically be picked up.
Mynda
