Power Query - Data Extraction + Column Formation

[nayseem]

Hello Excel World,

Hoping someone can help me out or point me in the right direction! I am definitely a noob to Excel but have used YouTube to guide me to where I am so far with this project.

I am creating an event register/tracker that is "self-sufficient" meaning that I want it to identify events based off a criteria and to create an additional tab with just a subset of those identified events. To do this, I have used PowerQuery to filter out the desired events and it works perfectly. However, these events need a bit more investigation and I am hoping to add columns within the PowerQuery with drop down lists.

The issue I am encountering is that even though I have duplicated other columns to use, when the data set refreshes, my drop down lists vanish and the previous (original data set) column's preferences re-appear.

What can I do to prevent this from happening? Is there a way to create a subset of data with additional columns powered by drop down lists?

I've spent over 5 hours in YouTube videos and can't find the answer. Thanks in advance!

 

[Matt Allington]

I don't fully understand what you are trying to do. Are you saying that the query returns a table to Excel, then you are adding extra columns to that table and adding a drop down list to the new columns? Why does it need to be a drop down list - I assume each row only has a single value. So does the drop Down simply make your data entry easier? Assuming so, is the issue that your entries don't stick? Maybe my article on self referencing tables will help you.
Self Referencing Tables in Power Query - Excelerator BI

 

[nayseem]

Hi Matt,

Thanks for responding. The drop down is to provide standardization and prevent errors for running Pivot Tables off the data. The link you sent is great but ideally the drop-down menus would not be overwritten by the duplicated column. I wonder if creating a form to input the data would be much easier. Do you have a sense of the build time for that?

What is being asked of me is incredibly intricate work which a platform/software needs to be developed for but we don't have the resources to create an ideal state product.

 

[Matt Allington]

I don't see any reason why you can't turn the comment column in the table to a drop down using data validation. Try it and see what happens. Otherwise you could add it after refresh with VBA. 30 mins work.