New table created on every PowerPivot refresh?

My PowerPivot workbook, has started generating new tables in the PowerPivot data model on refreshes.

It's pulling from an SQL Server. When opened up the workbook

I find a dozen tables named, "Query1", "Query2", "Query3" etc.

Plerase assist.

December 3rd, 2014 12:15pm

I see this issue on other forums, but didn't get any workarround.

I will appreciate any Ideas from this forum.

Thanks


Free Windows Admin Tool Kit Click here and download it now
December 8th, 2014 11:03pm

Hi Ori. Can you send us a Frown from the PQ ribbon in Excel and include your formulas? Also, can you include a screenshot of the Excel Connections UI?

Thanks,

Ehren

December 10th, 2014 11:32pm

Hi Ehran

Thanks for your reply


Here are my formulas from the workbook analysis (you see many duplications, I assume it's due to the duplication error I raised in this ticket)

 


Sheet Name Cell Address Formula Value
Dashboard M4 ='Calculations For Dashboard'!M55 12/18/2014 5:43:29 AM
Dashboard B6 =GETPIVOTDATA("[Measures].[Sum of MTD_Cnt]",'Calculations For Dashboard'!$B$71,"[Domains].[DomainAssignType]","[Domains].[DomainAssignType].&[Wix-DNS]") 23306
Dashboard B8 ='Calculations For Dashboard'!G73 0.823693469
Dashboard B10:B12 ='Calculations For Dashboard'!H73 0.060134643
Dashboard B15:B17 ='Calculations For Dashboard'!P26 0.99060965
Dashboard B20:B22 ='Calculations For Dashboard'!R38 0.461127641
Dashboard B25 =GETPIVOTDATA("[Measures].[Sum of MTD_Cnt]",'Calculations For Dashboard'!$B$71,"[Domains].[DomainAssignType]","[Domains].[DomainAssignType].&[Other-DNS]") 21289
Dashboard B27 ='Calculations For Dashboard'!G72 0.889614355
Dashboard B29:B32 ='Calculations For Dashboard'!H72 0.065675527
Dashboard B35:B38 ='Calculations For Dashboard'!J15 0.699938936
TLD G2 =IF(D6=1,A6,"")
TLD H2 =IF(F6=1,B6,"")
TLD I2 =IF(G6=1,C6,"")
TLD J2 =IF(H6=1,#REF!,"")
TLD K2 =IF(I6=1,D6,"")
TLD L2 =IF(J6=1,F6,"")
TLD M2 =IF(K6=1,G6,"")
TLD G3 =IF(D7=1,A7,"")
TLD D6 =(C6-B6)/B6 -0.074579965
TLD D7 =(C7-B7)/B7 -0.023096664
TLD D8 =(C8-B8)/B8 -0.099430574
TLD D9 =(C9-B9)/B9 -0.068461538
TLD D10 =(C10-B10)/B10 0.018209408
TLD D11 =(C11-B11)/B11 0.088082902
TLD D12 =(C12-B12)/B12 -0.27184466
TLD D13 =(C13-B13)/B13 0.143356643
TLD D14 =(C14-B14)/B14 -0.140127389
TLD D15 =(C15-B15)/B15 -0.251908397
TLD D16 =(C16-B16)/B16 0.096385542
TLD D17 =(C17-B17)/B17 0.194805195
TLD D18 =(C18-B18)/B18 #DIV/0!
TLD D19 =(C19-B19)/B19 -0.42
TLD D20 =(C20-B20)/B20 0
TLD D21 =(C21-B21)/B21 -0.03030303
TLD D22 =(C22-B22)/B22 -0.366666667
TLD D23 =(C23-B23)/B23 0.411764706
TLD D24 =(C24-B24)/B24 0
TLD D25 =(C25-B25)/B25 #DIV/0!
TLD D26 =(C26-B26)/B26 0
TLD D27 =(C27-B27)/B27 #DIV/0!
TLD D28 =(C28-B28)/B28 #DIV/0!
TLD D29 =(C29-B29)/B29 #DIV/0!
Calculations For Dashboard E6 =D6/(D6+C6) 0.515938219
Calculations For Dashboard E7 =D7/(D7+C7) 0.524883875
Calculations For Dashboard E8 =D8/(D8+C8) 0.505410122
Calculations For Dashboard E9 =D9/(D9+C9) 0.522117729
Calculations For Dashboard J9 =H9/I9 0.751969688
Calculations For Dashboard E10 =D10/(D10+C10) 0.515224359
Calculations For Dashboard J10 =H10/I10 0.738580645
Calculations For Dashboard E11 =D11/(D11+C11) 0.544542033
Calculations For Dashboard J11 =H11/I11 0.747100678
Calculations For Dashboard E12 =D12/(D12+C12) 0.5499114
Calculations For Dashboard J12 =H12/I12 0.700590536
Calculations For Dashboard E13 =D13/(D13+C13) 0.580594327
Calculations For Dashboard J13 =H13/I13 0.71373284
Calculations For Dashboard E14 =D14/(D14+C14) 0.58996063
Calculations For Dashboard J14 =H14/I14 0.711985308
Calculations For Dashboard E15 =D15/(D15+C15) 0.604485863
Calculations For Dashboard J15 =H15/I15 0.699938936
Calculations For Dashboard E16 =D16/(D16+C16) 0.592684954
Calculations For Dashboard J16 =H16/I16 0.723843912
Calculations For Dashboard E17 =D17/(D17+C17) 0.555121188
Calculations For Dashboard E18 =D18/(D18+C18) 0.541214058
Calculations For Dashboard E19 =D19/(D19+C19) 0.554811206
Calculations For Dashboard E20 =D20/(D20+C20) 0.585404548
Calculations For Dashboard P20 =O20/N20 0.929620772
Calculations For Dashboard E21 =D21/(D21+C21) 0.574963305
Calculations For Dashboard P21 =O21/N21 0.957114909
Calculations For Dashboard E22 =D22/(D22+C22) 0.510109968
Calculations For Dashboard P22 =O22/N22 0.98871974
Calculations For Dashboard E23 =D23/(D23+C23) 0.492360581
Calculations For Dashboard P23 =O23/N23 0.991683043
Calculations For Dashboard E24 =D24/(D24+C24) 0.500432526
Calculations For Dashboard P24 =O24/N24 0.991656485
Calculations For Dashboard E25 =D25/(D25+C25) 0.559409594
Calculations For Dashboard P25 =O25/N25 0.991925672
Calculations For Dashboard E26 =D26/(D26+C26) 0.572627502
Calculations For Dashboard P26 =O26/N26 0.99060965
Calculations For Dashboard E27 =D27/(D27+C27) 0.499605367
Calculations For Dashboard P27 =O27/N27 0.979887706
Calculations For Dashboard E28 =D28/(D28+C28) 0.499815702
Calculations For Dashboard E29 =D29/(D29+C29) 0.502696456
Calculations For Dashboard E30 =D30/(D30+C30) 0.481684211
Calculations For Dashboard E31 =D31/(D31+C31) 0.493466899
Calculations For Dashboard E32 =D32/(D32+C32) 0.535260931
Calculations For Dashboard E33 =D33/(D33+C33) 0.547169811
Calculations For Dashboard E34 =D34/(D34+C34) 0.503470804
Calculations For Dashboard E35 =D35/(D35+C35) 0.50422427
Calculations For Dashboard E36 =D36/(D36+C36) 0.460951689
Calculations For Dashboard E37 =D37/(D37+C37) 0.46223565
Calculations For Dashboard G72 =GETPIVOTDATA("[Measures].[Sum of connected_to_site]",$E$71,"[Domains].[DomainAssignType]","[Domains].[DomainAssignType].&[Other-DNS]")/GETPIVOTDATA("[Measures].[Sum of MTD_Cnt]",$B$71,"[Domains].[DomainAssignType]","[Domains].[DomainAssignType].&[Other-DNS]") 0.889614355
Calculations For Dashboard H72 =(D72-C72)/C72 0.065675527
Calculations For Dashboard G73 =GETPIVOTDATA("[Measures].[Sum of connected_to_site]",$E$71,"[Domains].[DomainAssignType]","[Domains].[DomainAssignType].&[Wix-DNS]")/GETPIVOTDATA("[Measures].[Sum of MTD_Cnt]",$B$71,"[Domains].[DomainAssignType]","[Domains].[DomainAssignType].&[Wix-DNS]") 0.823693469
Calculations For Dashboard H73 =(D73-IF(C73>0,C73,D73))/IF(C73>0,C73,1) 0.060134643




Here is the data model (ignore all the wix_bi querries - these are the duplicated data sets)



This is the connection string:

Provider=SQLOLEDB.1;Persist Security Info=True;User ID=wixreader;Initial Catalog=wix_bi;Data Source=zeus10.wixpress.com;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ORI-T-PC;Use Encryption for Data=False;Tag with column collation when possible=False;Password=**********




  • Edited by Ori Tal Thursday, December 18, 2014 12:21 PM
Free Windows Admin Tool Kit Click here and download it now
December 18th, 2014 11:26am

Hi Ori. If you're not using Power Query, I'd recommend posting your question on the Power Pivot forum.

Ehren

December 19th, 2014 12:01am

Posted on Power Pivot.


  • Edited by Ori Tal Sunday, December 21, 2014 7:47 AM
Free Windows Admin Tool Kit Click here and download it now
December 21st, 2014 10:43am

Any suggestions for Ori in Power Pivot? Is this possible?

Thanks!

February 4th, 2015 10:38pm

Hey Ori, would it be possible for you to share the workbook?
Free Windows Admin Tool Kit Click here and download it now
February 9th, 2015 6:44am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics