Create caclulated table in power pivot

[danextian]

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.

[LivioLanzo]

@danextian

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

[NoSpaces]

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?

[NoSpaces]

I found out why it did not allow to Import as table, but it still does not work. 🤷‍♀️

Following your guide:

1. Create table with >1 columns (1 column just did not work!)
   
2. 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 table
   

   Then it can be imported as a Table

   
3. Then you can right-click on the newly created table and edit DAX
   
4. 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.

[Jarid-Mckenzie]

Just to close the loop on this, You need to add the expression "EVALUATE" at the beginning of the Values statement.

[danextian]

Hi @LivioLanzo,

Thanks for the response. What I am trying to do is create a caclulated table from an already existing table. 

[LivioLanzo]

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