Excel PowerPivot/Tabular Error - The dimension Cardinality was not found in the cube

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)!