Powe Query: Add Tracking Columns to power query results table

[tsheets]

Hi all,

I am not really sure if this is an Excel question or Power Query question, but, here goes.

I have some data that is refreshed daily (download report) and I would like to process through Power Query.

I can get the results I am looking for with Power Query, but, the problem is, I need to track the status of the results.

I tried loading the power query results to a table in excel and adding a column to track the status (blank, in process, done, etc..). I am fine with just typing the status updates as needed. However, when I refresh the query results, the additional column doesn't work. Then, I tried creating another table, based on the power query table (or query under existing connections) and added the tracking column, the column stayed as I wanted, but, the new records cause existing status entries to become misaligned.

Really, I think the answer is a database with a related status table, but, I don't have the Access skills to go that route.

So, how can I take power query results, add one or more tracking / status columns, and be able to refresh the data without the existing status entries ending up on the wrong rows?

Thanks!

Tim

 

[tsheets]

forgot to add, I am using excel 2010.

 

[tsheets]

Wow, first post and I've already stumped the mrexcel message boards???

I have done more playing with this and believe I've found a bug.

I am currently loading the source data from an .xls file to a connection. Then have a table based on the connection as a separate step. This allows me to insert additional status/tracking columns. Also, I added a step to the power query steps to specify a sort. Now, with the limited testing I've done, it appears that my tracking data (added columns) stays with the right rows, with one exception.

If I add a status to the last record in the table, when I refresh the table to bring in new data, the information in the added column (not in the source file / PQ definition) gets removed from the original row, and added to the new (after update) last record. If nothing is in the added columns of the last row, removing a blank and moving it to the new last row doesn't hurt anything.

If anyone has any other ideas how to accomplish this, I'm all ears.

Thanks!

 

[miguel.escobar]

is there any chance that you can share a sample file with us? also, is there any way that you can programatically insert those new columns? my main question is, do you REALLY need to insert those columns manually or can it all happen based on some logic ?

Note: I tried but I couldn't reproduce the bug on my end.

 

[tsheets]

I am not sure how to share a sample file. But, if someone can let me know how to do that, I sure can.

The columns could be added at any point, really. I don't want to mess with the source data, though, and I couldn't figure out how to do it in PQ, so adding to the resulting excel table based on the connection seemed to be the easiest way and only needs to be done once.

The tracking columns would be for prioritizing (the data is free-form so assigning a priority would be a judgement call) and status (blank, in process, complete, etc..).

 

[scottsen]

Use OneDrive, GoogleDrive or Dropbox to share your file.

 

[tsheets]

Thanks, scottsen.

I uploaded source1.xlsx and pq-test1.xlsx to my OneDrive.

The PQ query might try to load the source from my desktop folders since that is where it was created.

Here is the link: https://onedrive.live.com/redir?resid=1F799E756D38084!105

Feel free to download and add new items to source, then right-click in the pq-test table and select refresh.

 

[scottsen]

I'm a bit out of my depth here -- but from what I can tell... PQ -> Table -> {modify} -> Refresh just isn't supported.

However... PQ -> Power Pivot -> Table -> { modify } -> Refresh ... seems to work fine.

You might play with that and see if it helps your cause?

Ugh. You are using 2010. I ... uh, don't know how to go from Power Pivot to a table in 2010.

Um, Miguel ? Any ideas here?

 

[miguel.escobar]

Hey guys

Sorry for the long hiatus & happy new year. I just tested this and it seems to be either a bug or something to work as designed by the PQ team. Could you send a frown to the PQ team?

 

[tsheets]

Thanks for looking / testing, Miguel!