Some individual cells in the final output table need to be modified and survive through refresh

omo
Posts: 8
Joined: Mon Jun 05, 2017 6:57 am

Some individual cells in the final output table need to be modified and survive through refresh

Postby omo » Mon Jun 05, 2017 9:43 pm

Here’s my workflow: I paste some data in several structured tables in a single excel file. I then create several queries from these tables, merge and append them and load the final table into a worksheet (all this within the same file). I then go over the final table and change the values in 3 or 4 cells (this cannot be done in power query unless there is a way to modify individual cells). Now I have the table ready for a pivot. This table is, say, for Month 1. A month later the time comes to make a report for Month 2. I paste Month 2 data into the same structured tables in the same file at the bottom of the tables just below the data for Month 1. Now my structured tables have the data for two months. I do a refresh and now I have the final table for two months. Except that the changes I did by hand for Month 1 are now gone.
How to use power query and still keep the changes? One strategy I see is pasting the data for a new month in a separate file and then merge the cumulative final table (which must never be refreshed so the hand-made data is not lost) with the new month's table. Is there a better strategy?

PhilC
Posts: 145
Joined: Tue Sep 09, 2014 8:13 am

Re: Some individual cells in the final output table need to be modified and survive through refresh

Postby PhilC » Wed Jun 07, 2017 3:45 pm

Hi omo,

Google "Power Query incremental refresh" and you might find some useful resources to fit your specific situation. Matt has one approach but could not find the link with a quick scan.

Some aspects to consider:
- can the needed edits to the data be done in the original tables, so when processed they end up in the final table and no manual change is required?

- can you write business rules to achieve the edits you are making manually? You mention changing specific cells, this can be done if a business rule / logic can be defined specifically enough.

- as you suggested, have a "template" file that you process the data in, with a final table, manually edited as required. Save the Month file in a different folder. Then use Import from Folder, and have it only import that final, edited table. Not a great solution as someone might mistakenly refresh a Month and overwrite the manual changes.

- Have the processing file that you already have, make the manual changes to the resulting output, then copy the result to yet another table (same or different file). Each month, copy the final result to this manual cumulative table that is not linked to PQ at all.

If you create a file with an example of the data and the processing, including the manual edits, it might become obvious of a better approach.

Cheers
Phil

MattAllington
Posts: 845
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Some individual cells in the final output table need to be modified and survive through refresh

Postby MattAllington » Wed Jun 07, 2017 3:48 pm

Phil and I were replying at the same time. Here is my spin

My first question is where does the data you are pasting come from anyway? is it possible to use Power Query to grab that rather than cut and paste all the time?

Regarding the minor changes, one option is to use a self referencing table as I describe here. http://exceleratorbi.com.au/self-refere ... wer-query/

Instead of adding a comments column, you could add a substitute column. then load the data back into PQ as I describe, and write a custom column that takes the value from the substitute column if it exists, or from the regular column otherwise.

Hope that makes sense.
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

omo
Posts: 8
Joined: Mon Jun 05, 2017 6:57 am

Re: Some individual cells in the final output table need to be modified and survive through refresh

Postby omo » Fri Jun 09, 2017 5:38 am

Phil and Matt, thank you for your detailed replies. Self referencing is really an ingenious method, both the variant suggested by Phil (as detailed, for example, here: https://blog.oraylis.de/2015/05/increme ... for-excel/) and by Matt. I have been checking them out, trying to see which one suits me more. I am still in two minds.
Answering your questions, the data can be edited in the source tables, but this involves some inconveniences further down the line. The process cannot be formalized. I am working on grabbing the data by Power query, it's an online database (rather online reports) and they tell me they are not sure if they can give me access to their databases.
I'll do some more test scenarios and get back to you. Again thank you very much, you've been very helpful.

omo
Posts: 8
Joined: Mon Jun 05, 2017 6:57 am

Re: Some individual cells in the final output table need to be modified and survive through refresh

Postby omo » Sat Jun 10, 2017 10:58 pm

Matt, somehow I cannot replicate what you describe in your blog, the comments column gets duplicated (please see the file attached).

For me the best solution would be this: add a column to the final table (the table produced by Power query). This column contains a formula, but there is hard-code in some cells. That’s it.
Unfortunately, even though the added column is not part of Power query, it is still refreshed as part of the table and hard-code is eliminated and I am back to square one.
Attachments
3.xlsx
(167.13 KiB) Downloaded 6 times

MattAllington
Posts: 845
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Some individual cells in the final output table need to be modified and survive through refresh

Postby MattAllington » Sat Jun 10, 2017 11:14 pm

Yes, that is a recent change. Just delete the new column that gets created, and it will all work
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

omo
Posts: 8
Joined: Mon Jun 05, 2017 6:57 am

Re: Some individual cells in the final output table need to be modified and survive through refresh

Postby omo » Sun Jun 11, 2017 12:23 am

Ok.
I realized that your approach works only if each row's unique identifier is located in the source tables (and this is often not the case, especially when there is more than one table). If instead of that you use an index column in Power query, the method will not work.

MattAllington
Posts: 845
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Some individual cells in the final output table need to be modified and survive through refresh

Postby MattAllington » Sun Jun 11, 2017 11:20 pm

Unfortunately this is true. However as long as there are not any duplicates, you may be able to concatonate all columns into a unique ID. It would be a big column, but I am guessing it would work.
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest