Hi guys. I am quite new to PowerPivots and wish to learn about it a bit more, but face few constant issues and hope you can help with that.
So basically I have Windows 7 64 bit + MS Office 2010 Standard 32 bit
I have installed PowerPivot for excel 2010 (x86 - so 32 bit version, as my excel)
It launches and works quite well.
Now - I am trying to do some stuff, and get few errors. I import data from external excel file (.xlsx) and Excel is set to default save to xlsx.. file I import has 20-30 columns and more than 5000 lines. it has some of the cells empty. But there are no completely empty lines/colums.
It imports well and displays as it should be.
1) When i try to create pivot chart i get this error:
However, if I create Pivot table with slices from the same data - everything is alright.
It creates new sheet, table, I can use it as it should be (with the slices etc.). I can even create pivot tables and then create Normal Chart from the pivot table.,.. but again - I cannot create pivot chart instantly. i have to use that workaround.
Found 4 solutions on the internet (adding calculation columns and deleting, default saving as xls, vertipaq folders (which I do not have) - nothing worked :/ I am behind proxy, by the way...
Any solution?
2) I try to import data from similar file (xlsx) which has 7 sheets.
5 sheets - each represent different product group, has more or less same data structure:
column 1 = name of customer
Column 2 = what type of customer
Column 3 = what customer is spending
Column 4 - Country
etc. up to 45 various columns
the number of columns varies a bit (+/-2 columns), depending for the product (so sheet 1 might have 1 column more than sheet 2)
Each sheet has more or less same customer names. Basically, it is split the way, that Customer, buys product A (and data is in Sheet 1) and Product B (Sheet 2) etc.
other sheets they have same customer names, but their structure is a bit different, but basically - all 7 sheets have few things in common - customer name, country, year, product type, etc.
it has some empty cells (cells with 0 as well). But there are no completely empty lines/colums.
I am loading data from this file to PowerPivot - it loads all the sheets and I see them in my PowerPivot sheet names. Everything is alright. All data is there, it is correct.
But when i try to create some relationships in tables (i.e. I would liek to have a relationship - customer name in all tables) I get error:
What am I missing here? How to solve it? is it even possible to have some sort of supermegapowerpivot which would connect data from 7 different sheets (ok - at least from 3) and let me have various cuts, slices and other data analysis magic?
thank you for any help!
So basically I have Windows 7 64 bit + MS Office 2010 Standard 32 bit
I have installed PowerPivot for excel 2010 (x86 - so 32 bit version, as my excel)
It launches and works quite well.
Now - I am trying to do some stuff, and get few errors. I import data from external excel file (.xlsx) and Excel is set to default save to xlsx.. file I import has 20-30 columns and more than 5000 lines. it has some of the cells empty. But there are no completely empty lines/colums.
It imports well and displays as it should be.
1) When i try to create pivot chart i get this error:
Code:
The Command was canceled. Please press F1 to get the help topic "Excel Window: Power Pivot Field List" for more details.
============================
Error Message:
============================
Exception from HRESULT: 0x800A03EC
============================
Call Stack:
============================
Server stack trace:
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at Microsoft.Office.Interop.Excel.Shapes.AddChart(Object XlChartType, Object Left, Object Top, Object Width, Object Height)
at Microsoft.AnalysisServices.Modeler.FieldList.GeminiRibbon.CreateChart(PivotCache cache, Worksheet workSheet, Int32 rowIndex, Int32 colIndex, Object pivotVersion, SASlicerHost slicerHost)
at Microsoft.AnalysisServices.Modeler.FieldList.GeminiRibbon.InsertPivot(String sandboxConnectionString, String location, String cubeName, MultiObjectsReportType reportType)
============================However, if I create Pivot table with slices from the same data - everything is alright.
It creates new sheet, table, I can use it as it should be (with the slices etc.). I can even create pivot tables and then create Normal Chart from the pivot table.,.. but again - I cannot create pivot chart instantly. i have to use that workaround.
Found 4 solutions on the internet (adding calculation columns and deleting, default saving as xls, vertipaq folders (which I do not have) - nothing worked :/ I am behind proxy, by the way...
Any solution?
2) I try to import data from similar file (xlsx) which has 7 sheets.
5 sheets - each represent different product group, has more or less same data structure:
column 1 = name of customer
Column 2 = what type of customer
Column 3 = what customer is spending
Column 4 - Country
etc. up to 45 various columns
the number of columns varies a bit (+/-2 columns), depending for the product (so sheet 1 might have 1 column more than sheet 2)
Each sheet has more or less same customer names. Basically, it is split the way, that Customer, buys product A (and data is in Sheet 1) and Product B (Sheet 2) etc.
other sheets they have same customer names, but their structure is a bit different, but basically - all 7 sheets have few things in common - customer name, country, year, product type, etc.
it has some empty cells (cells with 0 as well). But there are no completely empty lines/colums.
I am loading data from this file to PowerPivot - it loads all the sheets and I see them in my PowerPivot sheet names. Everything is alright. All data is there, it is correct.
But when i try to create some relationships in tables (i.e. I would liek to have a relationship - customer name in all tables) I get error:
Code:
The relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values.
============================
Error Message:
============================
The relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values.
============================
Call Stack:
============================
at Microsoft.AnalysisServices.Modeler.Controls.RelationshipController.formCreateRelationshipDialog_Confirming(Object sender, EventArgs e)
at Microsoft.AnalysisServices.Modeler.Controls.CreateRelationshipDialog.buttonOK_Click(Object sender, EventArgs e)
============================What am I missing here? How to solve it? is it even possible to have some sort of supermegapowerpivot which would connect data from 7 different sheets (ok - at least from 3) and let me have various cuts, slices and other data analysis magic?
thank you for any help!
