Forum Discussion
Imported data not aligning with manually entered data in table
I would like a table with 2 manually entered data columns and 1 imported data column as shown:
The imported data (just a single column table of data with the stock items) is the middle column: sterile stock room.
I need to regularly edit the data in the two outside columns (manually entered columns).
I type a new heading to the right of the source data to use the auto-expand table feature.
Then move the first column I create to the left of the source data.
The order of the middle column MUST stay in the source data order as shown (pivot tables auto sort and sum both functions i don't require and are difficult to correct)
When I edit the source data to add a stock item, I refresh the data in this sheet and the data in the two outside columns no longer aligns with the stock item it is meant to as shown below:
In the properties I have selected the 'add new rows for new data', as you can see, it did not add a new row with that data, only in the source data rows.
This seems like such a simple request, I have seen many people trying to do online, even on the Microsoft help thread the tech didn't understand because it worked on their end, but not for many users.
I greatly appreciate any advice you may have to get this done, thank you.
10 Replies
Yes, manually added data is not in sync with one returned by Power Query. You need to query table with manually added columns again, merge with source and return result on the same place. Technique is described here Self Referencing Tables in Power Query - Excelerator BI
- Thank you, I did see you personally reccomend this to someone in my searches.
I read it, tried to do it, but you can't add data after you have created the connection as it kept wiping any new data I entered when you refresh.
This would work if I didn't need to keep adding data.That's exactly the same method as Riny_van_Eekelen demoed in his sample, all shall work if you build the query correctly.
ScrubNurseTracey It's not really all that simple. You need to include a step that joins the stock list you connect to with the table that contains the edits.
The attached file demonstrates this. I trust that you can follow the applied steps. If not, come back here.
Riny_van_EekelenHelloooo,
Thank you so much for helping!
So, i'm attempting the steps which make sense, however....
how on earth did you create source2??? there is no additional table in the sheet (you cant load the final query into a table) and there are no add column steps for you to have added the no./ask/ha columns through query edit either. I am perplexed.
Another thing is I cant see which queries were loaded or connection only so i am trying to guess. As the result after merge is a single query on it's own.
I believe I should have it sorted once I know the above.
I am very appreciative!
Would it really be too much for excel to bring out a 'group individual data rows in table' button
ScrubNurseTracey Perhaps difficult to explain but first you just connect to the blue table and load it back to Excel. That will become Source2, the green table. Now you can add the manual columns. Next time around the original data connection (the blue table) will be updated, indexed and merged with the now expanded (green) table. It's a bit awkward to begin with but it does work.
By the way, the Source2 step is added in the Advanced Editor, so it's not added by pushing a button. The same applies to the merge step. You first merge the query with itself to get the basic code and then you have to edit the M-code to merge the table from the "previous step" with the table from step "Source2". Again, not easy to grasp at first, but it does work.
