Feb 25 2022 08:09 PM
Feb 25 2022 08:09 PM
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.
Feb 26 2022 12:52 AM
@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.
Feb 26 2022 01:12 AM
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
Feb 26 2022 04:11 AM
Feb 26 2022 07:02 AM
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
Feb 26 2022 07:17 AM
Feb 26 2022 07:58 AM
@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.
Feb 26 2022 08:05 AM
Feb 26 2022 08:10 AM
Feb 26 2022 08:18 AM
@ScrubNurseTracey Can only feel sorry, because I can't help you any further. It does take an effort to learn these things and can imagine you're not really in to it (yet).
Feb 27 2022 01:55 AM - edited Feb 27 2022 03:25 AM
Is there any chance you could record yourself creating this via PowerPoint screen capture and flick it to me??? hahaha:
(new PowerPoint > click to add first slide > delete preloaded formatting > insert > screen recording > Shift + Windows logo + F > Record > Do your thing > when finished, hover over top of screen and stop > save > send.
That way no teaching. Just be your amazing self, do it in 4 mins and send it through. Only if it doesn't take up too much of your time. Thank you again for everything you have already done for me!! I totally understand if you wont.
Email address removed would be preferred.
I promised this as a quality improvement initiative at work, have now shot myself in the foot because I can't do advanced excel formula.
Didn't think it would be needed to simply align the data over 3 columns, who knew?...... you did. My version is slightly different as I need to import the first 'myStock' table from the stock room list (separate file).
So when I update or change the stock room file it will refresh through to all the individual files linked to it.