The ask? Load the extracted and transformed Microsoft Access data into Microsoft Dataverse. But before performing the actual import, ask the important questions, “Could the data model be improved?” During the transform process, “Were there any column types that could be changed?”
Often enough, while auditing the data, it may come to light that a text column or two have repeated, unique values. This could be an opportunity to convert these text fields to Choice columns, turning them into dropdowns.
Now, why a Choice data type? Well, because they help reduce inconsistent data entries. Imagine a text field asking for person’s address. Excluding the obvious misspellings and unnecessary spacing, how many ways can people enter “Georgia”? At least 5, when there should only be one:
- Georgia
- GA
- G.A.
- Ga
- G.a.
So, Choice column it is. And now, ask the next important question, “Should the choices be local or global?”
Dataverse offers two ways to create a Choice column, excluding table lookups. (1) Local choices are columns created on the table itself. These columns are only usable by its parent table. Alternatively, (2) global choices can be created in solutions. These choices exist in the environment and can be used by any table in the environment.
Before creating the Choice column, the question essentially comes down to reusability. Are these choices a one-and-done, or will they be needed by other tables in the environment?
Conclusion:
Extracted data often reveals opportunities for transformation. Transformed data often results in an altered data model. With an altered data model, why not tweak the column types before the data is finally imported into Dataverse?
“You don’t make progress by standing on the sidelines, whimpering and complaining. You make progress by implementing ideas.”
Shirley Chisholm
#BlackLivesMatter
console.log('Charles'); 