Forum Discussion
Create caclulated table in power pivot
Hello,
I am not sure which forum to post this question as I couldnt find the one for Power Pivot in Excel. Power BI Desktop is very familiar with me but I am still exploring Power Pivot. Basically, what I am trying to do is create a calculated table in Power Pivot but I can't find this option. I don't even know if it is possible.
6 Replies
it is possible to create it using Excel as well.
You need to go to Data > Existing Connections > Tables > then select one table from the data model ( a small one and import it into excel )
Afterwards, right click this table and choose
Write your query and then import this table into the Data Model
Hi LivioLanzo,
Thanks for the response. What I am trying to do is create a caclulated table from an already existing table.
IF you want to use DAX then you need to import the original table into the Data Model and follow the process I described above
I have exactly this use case. I created a dummy table and imported it into the model.
Problem: Excel does not allow me to import Model data as Table. Table option is grayed out. Only allows Pivots. Do you know how to overcome this?
I found out why it did not allow to Import as table, but it still does not work. š¤·āāļø
Following your guide:
- Create table with >1 columns (1 column just did not work!)
- Importing a table supporting DAX actually worked without explicitly adding the dummy table to power pivot data model:
Existing Connections > (This Workbook) > Select the dummy tableThen it can be imported as a Table
- Then you can right-click on the newly created table and edit DAX
- But now I need to use DAX function VALUES, and it does not work
The error is: Syntax for 'VALUES' is incorrect.
I'm pretty sure the DAX is correct, because column BusinessCatalogs[Business Role ID] does exist.
- Create table with >1 columns (1 column just did not work!)
