modelling new table edit

[DavidMoss]

In PBI Desktop under modelling there is the option to create "New Table" using DAX calculated table functions. 

What are our editing/formatting options of that new table in the PBI UX ? (Apart from the obvious advanced DAX formulas)

I was just trying to create a dimension on the fly using this formula in the formulas bar of the new table option 

dimensionTable = distinct ( Facttable [dimensionColumn] )

Used to the good features in the edit query UX what can we do with these new tables added in this way as they do not appear as new queries in the Query Editor ?

[Anonymous]

I don't believe there is any way to round trip the newly created table into power query (the query editor).  Your choices are to "create the new table directly in power query... instead of via dax" or "create calculated columns via dax to add new columns to the calculated table"

[DavidMoss]

Hi @Anonymous thanks for your consideration but i was looking to understand if anyone had ways of enhancing the calculated tables created via the New Table feature. More interestingly to understand strategically what can be done to enhance my datamodel that maybe the community doesn't know about with this feature.

Please we are not looking for generic basic how to use Power BI advise 🙂

[Anonymous]

I have re-read your original question, and I'm not sure what you are asking.  I can confirm that you can't edit tables created via the New Table funtionlity in the Query Editor dialog.  But as far as "what can you do w/ the new table" -- well, anything you want/need?  Add calculated columns, write measures against them, create relationships etc.   A common usage would be... if I don't have a StoreMaster table, but have lots of Sales... it would make sense to use the New Table functionality to create a store master table and relate back to the Sales table.

But like I said -- your question isn't super clear to me.

[DavidMoss]

let me re-phrase the observation i started with:

"What are our editing/formatting options of that new table in the PBI UX ? (Apart from the obvious advanced DAX formulas)."

I'm happy to see you agree (A): 'that you can't edit tables created via the New Table funtionlity in the Query Editor dialog'. OK so that is eliminated.

We both know we can (B) enhance the table with DAX be it calculated columns etc....

Assuming A&B then more accuratley my question is :

How can we edit the data in the new calculated table ? It appears this is not possible.

When i say edit i mean to execute similar features that are in the Power BI Query Editor.

I am asking this question because when i created the caluclated table dimension i ended up with both 'blank' rows and 'Null' rows which when trying to use this table as a dimension in the datamodel it was not possible to use it as PBI sees blank and null as the same value hence the dimension is no longer 'Distinct' or unique.

So what i want to do is like what i can do in the Query Editor edit/ change /replace tumple values.

I hope that is clearer. ?

[DavidMoss · Accepted solution]

In the end i resolved to using some simpe DAX. Heres what i did.

1: So the original dimension table i extracted from my fact table as discussed above using Calculated table 'New Table' feature :
dimensionTable = distinct ( Facttable [dimensionColumn] )

2: Then in that new table i created a caluclated column to return a 1 if the value is 'null' or 'blank' inthe dimensionColumn

NullTest = IF('dimensionTable'[dimensionColumn]="",1,2)

And i used this table as a staging table for step 3

3: I created a further new calculated table the final dim_Dimension table to use in the datamodel with distinct values for the end client to slice fact table. I used once again the 'New Table' option using the dax;

dim_Dimension = FILTER('dimensionTable', 'dimensionTable'[NullTest] = 2)

It seems a bit of an excessive work around, but at least now i have dynamic on the fly dimensions in  the model.

PS some may say why didn't i just report off the original dimension from the fact table...well i don't like end client interacting with master fact table, in fact i like to keep all my dimensions seperate in a Kimball model architecture and all my measures seperate aswell.