column calculations doesn't work for the complete table after refreshing the data

%3CLINGO-SUB%20id%3D%22lingo-sub-1817827%22%20slang%3D%22en-US%22%3Ecolumn%20calculations%20doesn't%20work%20for%20the%20complete%20table%20after%20refreshing%20the%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1817827%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20made%20a%20calculation%20in%20a%20column%20of%20a%20table%2C%20this%20works%20fine%20as%20a%20start%2C%20but%20after%20refreshing%20the%20data%20the%20formula%20stops%20after%203500%20rows%2C%20instead%20to%20the%20end%20of%20the%20table%20which%20contains%206000%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECalculation%20is%20on%20automatic%2C%20proces%20is%20that%20I%20retrieve%20data%20out%20of%20a%20folder%20via%20power%20query%2C%20this%20creates%20a%20table%20in%20excel%20were%20I%20add%20some%20calculation%20too%2C%20in%20extra%20columns.%20At%20setup%20this%20works%20fine%20but%20after%20using%20different%20data%20the%20calculation%20in%20the%20added%20columns%20stops%20at%20a%20row%20of%20the%20previous%20data%20set.%3C%2FP%3E%3CP%3ESo%20it%20looks%20like%20that%20the%20table%20format%20stops%20at%20the%20old%20set%2C%20but%20if%20I%20enter%20a%20simple%20formula%20like%20cel%20%3D1%20this%20calculations%20is%20processed%20until%20the%20end%20of%20the%20new%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebest%20regards%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMark%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELooking%20forward%20for%20an%20answer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1817827%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1817926%22%20slang%3D%22en-US%22%3ERe%3A%20column%20calculations%20doesn't%20work%20for%20the%20complete%20table%20after%20refreshing%20the%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1817926%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F816464%22%20target%3D%22_blank%22%3E%40mark1232445%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EColumn%20added%20to%20the%20table%20returned%20by%20Power%20Query%20is%20not%20in%20sync%20with%20the%20table%2C%20they%20live%20independently.%20Perhaps%20that's%20the%20reason.%20Perhaps%20something%20else%2C%20without%20the%20file%20it's%20hard%20to%20say.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1817930%22%20slang%3D%22en-US%22%3ERe%3A%20column%20calculations%20doesn't%20work%20for%20the%20complete%20table%20after%20refreshing%20the%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1817930%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F816464%22%20target%3D%22_blank%22%3E%40mark1232445%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%2C%20I%20wasn't%20able%20to%20reproduce%20the%20fault.%26nbsp%3B%20Even%20when%20I%20removed%20columns%20from%20the%20source%20data%20as%20well%20as%20adding%20rows%20(needed%20to%20repair%20the%20query)%2C%20the%20formula%20propagated%20throughout%20the%26nbsp%3Badditional%26nbsp%3B%20column%20flawlessly.%26nbsp%3B%20Maybe%20submit%20the%20sheet%20to%20MS%20with%20a%20'frown'%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have made a calculation in a column of a table, this works fine as a start, but after refreshing the data the formula stops after 3500 rows, instead to the end of the table which contains 6000 rows.

 

Calculation is on automatic, proces is that I retrieve data out of a folder via power query, this creates a table in excel were I add some calculation too, in extra columns. At setup this works fine but after using different data the calculation in the added columns stops at a row of the previous data set.

So it looks like that the table format stops at the old set, but if I enter a simple formula like cel =1 this calculations is processed until the end of the new table.

 

best regards

 

Mark

 

Looking forward for an answer

4 Replies
Highlighted

@mark1232445 

Column added to the table returned by Power Query is not in sync with the table, they live independently. Perhaps that's the reason. Perhaps something else, without the file it's hard to say.

Highlighted

@mark1232445 

Sorry, I wasn't able to reproduce the fault.  Even when I removed columns from the source data as well as adding rows (needed to repair the query), the formula propagated throughout the additional  column flawlessly.  Maybe submit the sheet to MS with a 'frown'?

Highlighted

@Sergei Baklan 

We seem to have given conflicting answers.  Mine is not based on any theoretical knowledge, it is just something I have tried a couple of time to see what happens.  I was pleasantly surprised, but maybe I was just lucky.

Highlighted

@Sergei Baklan  thks for your response

 

added a sub set of the file the problem occurs in the column unit seq number made, formule is to add an seq number. this formula works fine upto row 3491 there after it stops.

 

The columns marked as blue header are added extra.

 

hope this will help