Some of the new data import options that PowerApps provides us is nice. Perhaps the most significant area of innovation concerns the functionality available within Data integration projects. Full Power Query capability is now available to us when importing data, giving us a range of features that, previously, we would have dreamed of having. Now, we can do the following with only a few button presses:
- Dynamically add on new columns, based on conditional logic.
- Convert data to a range of different types, such as numeric, boolean and date/time.
- Remove problematic rows of data or fix them by replacing them with custom values.
- Merge two separate data sources into a single, unified dataset.
All told, this functionality provides a smooth runway for those who have worked previously with Power BI and are looking to turbo-charge their PowerApps journey. To build out your firstĀ Data integration project, navigate to the PowerApps portal and select theĀ Data ->Ā Data Integration option. From here, you can then click the New data integration projectĀ button to start bringing your data into the application:
Data imported in this fashion can then be straightforwardly loaded into a Common Data Service (CDS) entity of your choosing - either an existing or a brand new one. For those interested in finding out more about the capabilities included within Power Query, my recent series covering Microsoft Exam 70-778 delves into this very subject area on several occasions.
Recently, I was experimenting with importing in some large - and very untidy - datasets into CDS, using the method outlined above. Little or no work had been carried out on the data itself before loading it up into the PowerApps portal. Thankfully, though, a lot of the tools available within the online Power Query editor were able to get the data into a more reasonable shape. All was going well until I began the process of importing the data into CDS. Rather than creating the entity/fields manually, I was feelingĀ very lazy and instead chose theĀ Load to new entity option. This action forces the application to go off and create the entity and my list of fields, inferring the required information from this based on my Power Query editor output. All was going well until I hit the following error message during the actual import stage:
The full error message is as follows:
There was an error while creating this entity. Details: Sql error: A validation error occurred. A string value provided is too long. CRM ErrorCode: -2147012607 Sql ErrorCode: -2146232060 Sql Number: 8152.
Unfortunately, this was not an occasion where the error message indicated a clear way forward to fix. I initially suspected that the issue was down to individual rows being too long for inserting into the CDS database, but quickly discounted this after evaluating the data in question. In the end, the issue turned out to be staring me right in the face and required placing my Dynamics CRM/Dynamics 365 Customer Engagement hat firmly on. Those well versed with creating custom fields within this application will recall that there is a fixed limit on the length of a columns Display Name and (Logical)Ā Name values - 50 and 41 respectively. When comparing this to the dataset I was working with, I observed something similar to what can be seen in the below example:
As this example demonstrates, we have an excessively long column name that breaches the enforced limit for custom fields. So, at this stage, our solution should be reasonably obvious - fix the offending column name!
The above example file will then get imported successfully, creating the entity and each of the fields successfully:
