Calculated table edit queries option not availalbe

[saltyfish]

Hello,

I have a calcualted table that I would like to merge with another table, but Edit Queries option is not available when I right click on the table.  Am I missing anything?

Shawn

[Sean]

Can you post a screenshot of what you are looking at?

[saltyfish]

StoreMonthlyNetSales is a calcuated table.  As you can see from the screenshot below, Edit Queries option is not available.

[Sean]

You are not in the Query Editor!

This will open the Query Editor in a New Window!

[saltyfish]

I'm in Query Editor, and the table is not there!

[Sean]

Ok I see now in the first screenshot you posted the cells of the table icon are actually shaded a bit blue/gray...

You've created this table in PBI to most likely assist in a calculation.

This is from a Test File...

You should have the data in that table in another Query though.

Can you tell us why you want to merge that table with a query?

[ImkeF]

If you use the Query editor (and it's M-language) for creating columns or additional tables (queries) they can then further be referenced in the query editor and the table view.

But it you create your calculated columns or table in the table view (using DAX) they can only be used in the table view. So this is a one-way-street.

This can be a bit confusing for beginners and raise the question why the left the option in DAX. An advantage of using the DAX-version is speed at loading for certain calculations.But you pay the price of not being able to reference those items (back) in the query-editor. 

So you need to rebuild that table in the query editor (using M) if you need to reference it there. 

[Sean]

@ImkeF I just don't get why he needs to merge this DAX created table (which seems to be aggregation of the Daily Sales table to Monthly level) with a Query? (If he aggregates to Annual Sales would he then want to merge that one too?) What's the purpose?

[ImkeF]

@Sean Agree, I don't get it either and my post wasn't meant to question anything said so far. Actually, I don't have time at the moment to dive into detailed questions like you helpfully raised 🙂

Just thought it could be helpful to quickly stress out the underlying principles: Think ahead before creating helper tables in the wrong place.

[Sean]

@ImkeF Actually I'm glad you repsonded and clarified this because I wasn't 100% sure - it's a one way street.

I suspected it was... but with all the Magic I've seen you do with M... you never know...

[maracles]

@ImkeF

Out of curiousity how do you build a similar table in M? I have a similar situation where I've created a table with DAX and on my to-do list is to rebuild as suggested. 

Is there an easy way to create an M formula or would I have to use the Query Editor interface to transform my original table again? 

My DAX is:

Unique AM2 = 
    ADDCOLUMNS(
        SUMMARIZE(
            'AM2', 
            [Order ID]
        ),
       "Word Count", CALCULATE(MAX('AM2'[Word Count])),
       "Fee", CALCULATE(MAX('AM2'[Academic Fee])),
       "First Date", CALCULATE(MIN('AM2'[Assigned Date])) 
)

Thanks. 

[ImkeF]

There is no translator available at the moment who would automatically translate DAX-statements to M. So you would have to do it in the query editor.

In this case it is just one dialogue: Group by (check Order ID to group by and the other cols as new cols with their respective aggregation operations (MAX, MIN).