Imported data not aligning with manually entered data in table

%3CLINGO-SUB%20id%3D%22lingo-sub-3215748%22%20slang%3D%22en-US%22%3EImported%20data%20not%20aligning%20with%20manually%20entered%20data%20in%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3215748%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20a%20table%20with%202%20manually%20entered%20data%20columns%20and%201%20imported%20data%20column%20as%20shown%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22table%20want.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F351394iD33C682E26A21AEF%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22table%20want.PNG%22%20alt%3D%22table%20want.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20imported%20data%20(just%20a%20single%20column%20table%20of%20data%20with%20the%20stock%20items)%20is%20the%20middle%20column%3A%20sterile%20stock%20room.%3C%2FP%3E%3CP%3EI%20need%20to%20regularly%20edit%20the%20data%20in%20the%20two%20outside%20columns%20(manually%20entered%20columns).%3C%2FP%3E%3CP%3EI%20type%20a%20new%20heading%20to%20the%20right%20of%20the%20source%20data%20to%20use%20the%20auto-expand%20table%20feature.%3C%2FP%3E%3CP%3EThen%20move%20the%20first%20column%20I%20create%20to%20the%20left%20of%20the%20source%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20order%20of%20the%20middle%20column%20MUST%20stay%20in%20the%20source%20data%20order%20as%20shown%20(pivot%20tables%20auto%20sort%20and%20sum%20both%20functions%20i%20don't%20require%20and%20are%20difficult%20to%20correct)%3C%2FP%3E%3CP%3EWhen%20I%20edit%20the%20source%20data%20to%20add%20a%20stock%20item%2C%20I%20refresh%20the%20data%20in%20this%20sheet%20and%20the%20data%20in%20the%20two%20outside%20columns%20no%20longer%20aligns%20with%20the%20stock%20item%20it%20is%20meant%20to%20as%20shown%20below%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Table%20bad.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F351393i1E7289D9C52BABF9%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Table%20bad.PNG%22%20alt%3D%22Table%20bad.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIn%20the%20properties%20I%20have%20selected%20the%20'add%20new%20rows%20for%20new%20data'%2C%20as%20you%20can%20see%2C%20it%20did%20not%20add%20a%20new%20row%20with%20that%20data%2C%20only%20in%20the%20source%20data%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20seems%20like%20such%20a%20simple%20request%2C%20I%20have%20seen%20many%20people%20trying%20to%20do%20online%2C%20even%20on%20the%20Microsoft%20help%20thread%20the%20tech%20didn't%20understand%20because%20it%20worked%20on%20their%20end%2C%20but%20not%20for%20many%20users.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20greatly%20appreciate%20any%20advice%20you%20may%20have%20to%20get%20this%20done%2C%20thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3215748%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3215919%22%20slang%3D%22en-US%22%3ERe%3A%20Imported%20data%20not%20aligning%20with%20manually%20entered%20data%20in%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3215919%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1318315%22%20target%3D%22_blank%22%3E%40ScrubNurseTracey%3C%2FA%3E%26nbsp%3BIt's%20not%20really%20all%20that%20simple.%20You%20need%20to%20include%20a%20step%20that%20joins%20the%20stock%20list%20you%20connect%20to%20with%20the%20%26nbsp%3Btable%20that%20contains%20the%20edits.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20file%20demonstrates%20this.%20I%20trust%20that%20you%20can%20follow%20the%20applied%20steps.%20If%20not%2C%20come%20back%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3215939%22%20slang%3D%22en-US%22%3ERe%3A%20Imported%20data%20not%20aligning%20with%20manually%20entered%20data%20in%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3215939%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1318315%22%20target%3D%22_blank%22%3E%40ScrubNurseTracey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20manually%20added%20data%20is%20not%20in%20sync%20with%20one%20returned%20by%20Power%20Query.%20You%20need%20to%20query%20table%20with%20manually%20added%20columns%20again%2C%20merge%20with%20source%20and%20return%20result%20on%20the%20same%20place.%20Technique%20is%20described%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceleratorbi.com.au%2Fself-referencing-tables-power-query%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3ESelf%20Referencing%20Tables%20in%20Power%20Query%20-%20Excelerator%20BI%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3216175%22%20slang%3D%22en-US%22%3ERe%3A%20Imported%20data%20not%20aligning%20with%20manually%20entered%20data%20in%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3216175%22%20slang%3D%22en-US%22%3EThank%20you%2C%20I%20did%20see%20you%20personally%20reccomend%20this%20to%20someone%20in%20my%20searches.%3CBR%20%2F%3EI%20read%20it%2C%20tried%20to%20do%20it%2C%20but%20you%20can't%20add%20data%20after%20you%20have%20created%20the%20connection%20as%20it%20kept%20wiping%20any%20new%20data%20I%20entered%20when%20you%20refresh.%3CBR%20%2F%3EThis%20would%20work%20if%20I%20didn't%20need%20to%20keep%20adding%20data.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3216486%22%20slang%3D%22en-US%22%3ERe%3A%20Imported%20data%20not%20aligning%20with%20manually%20entered%20data%20in%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3216486%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3EHelloooo%2C%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20helping!%3C%2FP%3E%3CP%3ESo%2C%20i'm%20attempting%20the%20steps%20which%20make%20sense%2C%20however....%3C%2FP%3E%3CP%3Ehow%20on%20earth%20did%20you%20create%20source2%3F%3F%3F%20there%20is%20no%20additional%20table%20in%20the%20sheet%20(you%20cant%20load%20the%20final%20query%20into%20a%20table)%20and%20there%20are%20no%20add%20column%20steps%20for%20you%20to%20have%20added%20the%20no.%2Fask%2Fha%20columns%20through%20query%20edit%20either.%20I%20am%20perplexed.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22source2.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F351427iDE021B809F8EA62C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22source2.PNG%22%20alt%3D%22source2.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAnother%20thing%20is%20I%20cant%20see%20which%20queries%20were%20loaded%20or%20connection%20only%20so%20i%20am%20trying%20to%20guess.%20As%20the%20result%20after%20merge%20is%20a%20single%20query%20on%20it's%20own.%3C%2FP%3E%3CP%3EI%20believe%20I%20should%20have%20it%20sorted%20once%20I%20know%20the%20above.%3C%2FP%3E%3CP%3EI%20am%20very%20appreciative!%3C%2FP%3E%3CP%3EWould%20it%20really%20be%20too%20much%20for%20excel%20to%20bring%20out%20a%20'group%20individual%20data%20rows%20in%20table'%20button%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3216521%22%20slang%3D%22en-US%22%3ERe%3A%20Imported%20data%20not%20aligning%20with%20manually%20entered%20data%20in%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3216521%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1318315%22%20target%3D%22_blank%22%3E%40ScrubNurseTracey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20exactly%20the%20same%20method%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bdemoed%20in%20his%20sample%2C%20all%20shall%20work%20if%20you%20build%20the%20query%20correctly.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I would like a table with 2 manually entered data columns and 1 imported data column as shown:

table want.PNG

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:

Table bad.PNG

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

@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.

@ScrubNurseTracey 

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.

@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.

source2.PNG

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 

That's exactly the same method as @Riny_van_Eekelen demoed in his sample, all shall work if you build the query correctly.

@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.

Hello,
I completed all steps in the link. I have just done it again a 2nd time.
If i was to sort the items, yes they do stay together.
HOWEVER, when i add a new stock item to the source data and refresh my connections,
The data will no longer be aligned where it is meant to. This is why I couldn't continue with this method the first time.
I promise I did every step twice now, exactly as listed. It does not work when adding new items to the source data.
OHHH, I'm a nurse so if it's not selecting options or using sum, vlookup or filter, than I'm never sure how to complete the formulas in the right way.
Yes i was wondering how you had another source without a merge step.
I do not know what an M-code is haha.

@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).

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.