Quick fix/resolution for something that I noticed when populating a PowerPivot model with data from an OData feed via PowerQuery. The feed had nested XML tables that I pivoted into the main resultset using the PowerQuery “expand”. The resulting column name by default is ParentField.ChildField
I left the column names as default, added it to a PowerPivot model and tried to join it using one of these fields (Job[Client.ID] => Client[ID]) and received the following error
============================
Error Message:
============================
Query (1, 183) The dimension '[Client.ID_Cardinality]' was not found in the cube when the string, [Client.ID_Cardinality], was parsed.
It seems power pivot doesn’t quote identifiers. Quick fix is to rename the pivoted column (Job[Client.ID]) to one that doesn’t include a period in the column name (Job[ClientID])
An example!
Imagine I’ve imported the following data into excel
and added it into a data model
Now I try to relate the Job table to the Client table using [Job]Client.ID => Client[ID]
Quite an error message.
But if in the Job table I rename Client.ID to ClientID
Success!
Something to keep in mind, especially when expanding nested XML tables using PowerQuery. Strip those periods (and probably other special characters)!
