Home

Data modeling and table lengths in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-752688%22%20slang%3D%22en-US%22%3EData%20modeling%20and%20table%20lengths%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-752688%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20an%20Excel%20solution%2C%20I%20bring%20in%20data%20from%20SQL%20connection%20to%20worksheet%201%20(WS%231)%2C%20thus%20the%20data%20set%20number%20of%20rows%20changes%20each%20time.%20A%20second%20%3CEM%3Eworksheet%20(WS%232)%3C%2FEM%3Eruns%20calculation%20on%20WS%231.%20My%20problem%20starts%20when%20the%20WS%232%20ends%20up%20with%20bunch%20of%20N%2FA%20or%20Errors%20dow%20at%20the%20button%20due%20to%20the%20table%20size%20not%20matching%20to%20the%20correspond%20number%20of%20rows%20pulled%20from%20WS%231.%20I%20suspect%20this%20must%20be%20a%26nbsp%3B%20clear%20question%20for%20pro%20data%20modelers%3F%20I%20appreciate%20any%20help.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-752688%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Edata%20modeling%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20Query%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-754078%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20modeling%20and%20table%20lengths%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-754078%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F70866%22%20target%3D%22_blank%22%3E%40Nima%20Mohandesan%3C%2FA%3E%26nbsp%3BWhat%20formulas%20are%20you%20using%20to%20arrive%20at%20the%20numbers%3F%20Maybe%20you%20can%20perform%20these%20calculations%20in%20Power%20Query%20instead%20of%20worksheet%20formulas%2C%20so%20they%20get%20added%20to%20all%20rows%20of%20the%20query.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20that%20is%20not%20an%20option%20and%20you%20want%20to%20pre-fill%20your%20calculation%20formulas%20without%20getting%20errors%2C%20you%20can%20wrap%20them%20in%20an%20IF%20statement%20that%20checks%20how%20many%20rows%20of%20data%20exist%20in%20the%20query.%20For%20example%2C%20if%20the%20query%20results%20start%20in%20column%20A%20in%20row%201%20of%20the%20sheet%20called%20%22Data%22%2C%20then%20you%20can%20do%20this%20on%20the%20calculation%20sheet%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3Dif(counta(Data!%24A%3A%24A)%26gt%3B%3Drow()%2C%20YourCalculation%2C%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%2C%20if%20the%20count%20of%20populated%20rows%20in%20column%20A%20is%20bigger%20than%20the%20row%20number%20of%20the%20current%20row%2C%20perform%20your%20calculation%2C%20otherwise%20return%20a%20blank.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet%20me%20know%20if%20that%20will%20work%20for%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Nima Mohandesan
Occasional Contributor

Hi,

 

In an Excel solution, I bring in data from SQL connection to worksheet 1 (WS#1), thus the data set number of rows changes each time. A second worksheet (WS#2)runs calculation on WS#1. My problem starts when the WS#2 ends up with bunch of N/A or Errors dow at the button due to the table size not matching to the correspond number of rows pulled from WS#1. I suspect this must be a  clear question for pro data modelers? I appreciate any help. 

1 Reply

@Nima Mohandesan What formulas are you using to arrive at the numbers? Maybe you can perform these calculations in Power Query instead of worksheet formulas, so they get added to all rows of the query. 

 

If that is not an option and you want to pre-fill your calculation formulas without getting errors, you can wrap them in an IF statement that checks how many rows of data exist in the query. For example, if the query results start in column A in row 1 of the sheet called "Data", then you can do this on the calculation sheet

 

=if(counta(Data!$A:$A)>=row(), YourCalculation,"")

 

So, if the count of populated rows in column A is bigger than the row number of the current row, perform your calculation, otherwise return a blank.

 

Let me know if that will work for you.