• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Add manually filled column to query output table|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Add manually filled column to query output table|Power Query|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryAdd manually filled column to query…
sp_PrintTopic sp_TopicIcon
Add manually filled column to query output table
Avatar
Patrick Star

New Member
Members
Level 0
Forum Posts: 1
Member Since:
May 21, 2020
sp_UserOfflineSmall Offline
1
May 21, 2020 - 11:50 pm
sp_Permalink sp_Print

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....

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
May 24, 2020 - 10:28 pm
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
3
May 25, 2020 - 6:15 pm
sp_Permalink sp_Print

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.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Yliack Herrera, Daryl Dizon
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 216
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Melanie Ford
Isaac Felbah
Adele Glover
Hitesh Asrani
Rohan Abraham
Anthony van Riessen
Erlinda Eloriaga
Abisola Ogundele
MARTYN STERRY
Rahim Lakhani
Forum Stats:
Groups: 3
Forums: 24
Topics: 6355
Posts: 27791

 

Member Stats:
Guest Posters: 49
Members: 32324
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x