Log In
Register
Home
New Member
May 21, 2020
Offline
Hey there,
I couldn't find anything on this topic. That could be beacause I don't know how to phrase my problem properly...
Problem:
There is a folder with lots of files which is updated reguarly.
I loaded the folder to query (import-folder-query) and manipulated the file names so I have the desired output-table.
Now I want to add a new column to said output-table in which I can add comments. Those comments do not follow any system so this step can't be automated in power query.
When updating the query (after new files are added to the folder) my custom column is messed up and entrys are not in the right rows anymore.
Solution A (did not work):
I tried adding and sorting by an index column in the import-folder-query based on the creation dates of the files. This will help if (and only if) all filters in the output-table are disabled and the output-table is sorted by index before refreshing, which is not practical. Also with this solution the last rows in the manual column will still be messed up when refreshing.
Solution B (did not work):
I tried loading the output-table including my manual column as source data into a new query and merged this query with my folder-import-query. This will duplicate my custom column and won't work therefore.
Solution C (haven't tried due to likely problem updating changed files):
I could create the output-table manually including the custom colum. Then append the data from my import-folder-query and when updating just append new rows withouth editing old ones. At least I think this could work. However, in this solution changed file-names (and therefore changes in my output-table) won't be updated this way.
I hope this makes clear what I'm trying to solve here..
Any ideas? Or is this just not how query is supposed to be used?
Kind regards
Patrick
Sorry for my bad English - I'm no native speaker....
July 16, 2010
Offline
Hi Patrick,
I've been giving this some thought, hence my slower than usual reply.
I think the best option is to have two tables, Table A contains the data you get from a file. Add an index column to this table.
Table B will be created in your workbook and it will contain your comments and the corresponding index column the comment relates to from Table A. You can then merge these two tables using the index column for the match.
The merged table will be your final output table that contains both the data from the folder and the comments.
I hope that helps.
Mynda
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Offline
Hi Patrick,
Indeed, as Mynda said, you need to have a manual table with comments and merge it with the data query from folder.
Using an index column might not work, because when you run the query again it is possible to have a different index number for the same row at the next run.
I would do something else:
identify the columns that make a row unique. There can be 1 or more columns, let's say a row becomes unique based on values from 5 columns. (Name+Department+Year+Month+EmployeeID)
In your manual table, you will need to have those columns and a new column for comments.
Whenever you need to add a comment, copy the values from those 5 key columns to manual table, then fill the comment you want.
Change the data query from folder, add a merge queries step: merge the data table with the comments table, with those 5 columns as merge keys.
Expand only the last comments column from the merged table, at this point the other 5 columns should be identical with the ones from data query so it makes no sense to expand the key columns.
This will give you flexibility, you can sort the data and filter it, the merge will still pick the correct comment.
1 Guest(s)
