- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Calculated table edit queries option not availalbe
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you post a screenshot of what you are looking at?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
StoreMonthlyNetSales is a calcuated table. As you can see from the screenshot below, Edit Queries option is not available.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You are not in the Query Editor!
This will open the Query Editor in a New Window!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I'm in Query Editor, and the table is not there!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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).
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
06-25-2024 03:02 PM | |||
06-03-2024 04:26 PM | |||
01-01-2019 03:38 AM | |||
Anonymous
| 12-28-2018 10:40 AM | ||
05-14-2024 06:27 AM |
User | Count |
---|---|
141 | |
115 | |
84 | |
63 | |
48 |