SOLVED

Working next to data loaded to excel with power query

New Contributor

(I'll try to provide enough backstory without overloading folks. Apologies if I don't quite get the balance right.)

 

I'm working with a list of data validations that are triggered by an automatic system when we upload data to an online reporting platform. I then have to distribute the list to the various people who get to validate the data and explain why it is correct.

 

I can download a list of all of the validations, and I've created a Power Query connection that will:

  • Take all of the exported lists,
  • Get the most recent status for each issue,
  • Filter out issues that have been closed or already have explanations, and
  • Display in excel all of the remaining issues that still need explanations.

I would like to set it up so that I can add a column for comments next to the imported data and share that excel file with the folks who need to provide explanations.

 

However, once an explanation has been uploaded, that issue won't be in the next download and will disappear from the workbook. Unfortunately, the comment that was put in the workbook will not, and so every other comment will be offset from the issue its associated with.

 

Does anyone have any ideas for how I can make those lines disappear entirely? Bonus points if they come back if the issue is re-opened, but that's not a requirement.

 

Here's a quick sample of the situation. Apologies for not adding real data, but the actual information is a combination of highly technical, very dense, and not actually relevant to getting the behavior I want.

 

JrBusinessAnalyst_0-1650045535229.png

 

 

 

3 Replies
best response confirmed by JrBusinessAnalyst (New Contributor)
Solution

@JrBusinessAnalyst 

Power Query is not in sync with columns added to returned table manually. Workaround is described here Self Referencing Tables in Power Query - Excelerator BI

@Sergei Baklan This is why I love these forums. I've been trying to figure this out for three days and hadn't managed to find the right words to google.

 

Thank you, this worked like a charm!