While I enjoy that now I can write PowerQuery expressions everywhere in Analysis Services, the fact that I don’t know what is available at hand when I embed an M script in my model — is a bit of an annoyance. Especially when that script ends up working in Data Tools and Power BI but spits up various errors during processing of a table.
Expression in partition ‘Partition’ in table ‘SharedExpressionTable’ references an unknown entity.
Being able to access the #shared variable would solve the issue. However, the only way to access it is by creating a cube level expression and call it from a partition level expression. This can be achieved in 3 ways:
- Via clicky-click Data Tools
- By creating a new cube with all the required expressions via a single TMSL script
- Or by creating an expression and a table based on that expression with some TMSL and XMLA (because TMSL doesn’t allow you to create all the objects that are available in tabular)
I’ll leave the first option for the brave ones, as I kept experiencing various problems when trying to use Power Query in SSDT 2017. As for the remaining two options, here are the scripts.
Option 2
To create a new cube [Mcube] with all the required prerequisites, run the following command in SSMS as an XMLA script:
The interesting bits are at line 8, a construct which creates a cube level Expression and line 36, an M script that calls the previously created expression via #sections[main] section of the document.
Option 3
If you’re not happy with polluting your AS instance with various transient cubes, you can always add a cube level expression to an existing table with the XMLA template below:
| <Batch Transaction="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> | |
| <Create xmlns="http://schemas.microsoft.com/analysisservices/2014/engine"> | |
| <DatabaseID>tab</DatabaseID> | |
| <Expressions> | |
| <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql"> | |
| <xs:element> | |
| <xs:complexType> | |
| <xs:sequence> | |
| <xs:element type="row"/> | |
| </xs:sequence> | |
| </xs:complexType> | |
| </xs:element> | |
| <xs:complexType name="row"> | |
| <xs:sequence> | |
| <xs:element name="Name" type="xs:string" sql:field="Name" minOccurs="0"/> | |
| <xs:element name="Description" type="xs:string" sql:field="Description" minOccurs="0"/> | |
| <xs:element name="Kind" type="xs:long" sql:field="Kind" minOccurs="0"/> | |
| <xs:element name="Expression" type="xs:string" sql:field="Expression" minOccurs="0"/> | |
| </xs:sequence> | |
| </xs:complexType> | |
| </xs:schema> | |
| <row xmlns="urn:schemas-microsoft-com:xml-analysis:rowset"> | |
| <Name>SharedExpression</Name> | |
| <Kind>0</Kind> | |
| <Expression>#shared</Expression> | |
| </row> | |
| </Expressions> | |
| </Create> | |
| </Batch> |
It is worth pointing out that the Kind of this expression is 0 (line 24), which could only mean that M is a first-class citizen in this new world.
Don’t forget to create a table that sources the data from SharedExpression expression.
| { | |
| "create": { | |
| "parentObject": { | |
| "database": "tab" | |
| }, | |
| "table": { | |
| "name": "SharedExpressionTable", | |
| "columns": [ | |
| { | |
| "name": "Name", | |
| "dataType": "string", | |
| "sourceColumn": "Name" | |
| }, | |
| { | |
| "name": "Value", | |
| "dataType": "string", | |
| "sourceColumn": "Value" | |
| } | |
| ], | |
| "partitions": [ | |
| { | |
| "name": "Partition", | |
| "dataView": "full", | |
| "source": { | |
| "type": "m", | |
| "expression": "#sections[main][SharedExpression]" | |
| } | |
| } | |
| ] | |
| } | |
| } | |
| } |
The rest of the action
To populate the SharedExpressionTable with the contents of #shared, you will have to process the [Partition] with another TMSL command or clicky-click SSMS, which is also a reliable choice.
To view the results, use DAX or navigate to SharedExpressionTable in your reporting tool of choice (which has to be Power BI 🙂 )
evaluate 'SharedExpressionTable'
Query Power 
Really enjoyed reading your article and if you had started with the “clicky-click Data Tools” it would have been even more intuitive. Two missed opportunities: a) show the #shared function in action in Query Editor and point out that SSDT uses the exact same editor as Power BI Desktop, except the ribbon is replaced with a menu and toolbar. b) Show where shared expressions live in a data model based on a Tabular Model Explorer screenshot. That would help the reader understand what kind of objects you are later creating with TMSL and XMLA. Of course, another missed opportunity is TOM. Especially when working with large M expressions, you’ll find the JSON-based TMSL rather inconvenient. TOM is a much better alternative if you are not afraid of some managed code.
Oh, and don’t ever use “#sections[main][SharedExpression]” as the arrangement of M elements is internal to the AS engine and may change at any time without notification, which would break your deployed models and that wouldn’t be a good experience. Instead, just refer to the shared expression by name. In your case: SharedExpression. Expressions are top-level metadata objects, just like data sources and tables. So their names are unique within the Tabular model. Hence, no need to refer to sections of an M document. Please don’t guide customers down any unsupported paths.
One last thing about SSDT: The new Get Data experience is still in preview . If you notice any stability issues, please send us your feedback! Easiest is to just email ssasprev at microsoft.com. In SSDT 17.2, a number of issues have been fixed. 17.3 will contain even more quality and performance improvements, and we are continuing the quality push, so especially right now getting your feedback is more important than ever.
Anyway, great article! Another interesting topic might be how to copy expressions from Power BI into Tabular. That would give you an opportunity to talk about the difference between data access functions in Desktop vs. DSRs in Tabular. Among other things, you might want to point out that Desktop offers no API but Tabular provides you full programmatic access to the M resources in the model. And then you could show that data sources are references in M expressions similar to shared expressions, i.e. simply by referring to their name directly.
Sorry for the long comment. 🙂
LikeLiked by 1 person
Thanks for all your comments Kay! By the way on API access, that will be my next attempt. PUT TMSL to the newly analysis services api endpoint. I’m sure that’s not recommended yet, but I have a feeling that this might just work.
Fair point on TOM and that I need to guide the users with some additional screenshots.
LikeLike