3 ms·

Technically you do have column types already[1], although only available via the data model[2] (another wholly underutilized feature set). Although it only hel

by cosmie 6y ago

Technically you do have column types already[1], although only available via the data model[2] (another wholly underutilized feature set). Although it only helps to an extent, since it doesn't prevent bad data from being input into a table, and supports so many implicit conversions (listed out in [1]) that undesired behavior is still possible. Plus it enters into a little known area of Excel rather than baked into the customary workflows. That said, it still comes in handy. A typical pattern of mine for worksheets I have to release into the wild (i.e. no telling who will end up (ab)using it and when it'll eventually come back to me in a bastardized form to salvage) is: 1. Create table(s) for raw/user-inputted data 2. Use that table as a source for PowerQuery 3. Leverage PowerQuery for type checking, data normalization validation, and data joining. 4. Output to a new table on the worksheet, referencing this instead of the original table for all formulas. Also optionally putting complex logic into the PowerQuery steps, that way the "output" table is ready for end-use and doesn't require any complex and error-prone formulas or anything. Even without leveraging the data model and DAX functions, this works out super well. More sophisticated users can easily modify the PowerQuery steps, and less sophisticated users are protected from accidental mistakes by either using it as-is or reaching out for help earlier on, before there's a crisis for assistance. [1] https://support.microsoft.com/en-us/office/create-a-data-model-in-excel-87e7a54c-87dc-488e-9410-5c75dbcb0f7b https://support.microsoft.com/en-us/office/create-a-data-mod... [2] https://support.microsoft.com/en-us/office/data-types-in-data-models-e2388f62-6122-4e2b-bcad-053e3da9ba90 https://support.microsoft.com/en-us/office/data-types-in-dat...

clairity 6y ago
> "1. Create table(s) for raw/user-inputted data" you can still be exposed to data munging on input/import, due to all sorts of formatting issues (like zip codes losing their leading zeroes or invalid phone numbers due to misaligned formatting).
- cosmie 6y ago
  Very true, and that's why I prefaced my comment with "only helpful to an extent". In some cases, data validation[1] on the cells directly can help. Particularly if the data is going to be manually entered by the end user, rather than a bulk copy/paste job from elsewhere. In other cases, you apply defensive checks within the PowerQuery processing. If a field is supposed to contain US-formatted phone numbers, you can explicitly set the column type to text, split at dashes and keep the first part, verify it only contains numerical content, optionally check for non-sense values (all zeros, all nines, 12345, etc), then left pad it with zeros to ensure they're all 5 digits. You can also package up all of those steps into a function that can easily be added into future worksheets via copy/paste and save you from re-creating the wheel. And depending on the data itself, it may be handy to call out to a third-party API for validation/normalization. To maintain sanity, you can also create a helper column for validation. If any of the columns can't be "safely" normalized or validated, you update this field with details on what failed. At the end, split out any records where that column is not null into its own table. That way you both filter those records out of the "final" table, and can also easily add in an Exceptions Report section to the spreadsheet, clearly calling out the rows that had issues and providing the error details to the end user. It sounds complicated, and it is compared to the alternative (Excel's undefined behavior silently swallowing it). But it's actually incredibly straightforward to implement and a fairly natural design pattern for someone coming from a software development background. You just start treating Excel as a UI and PowerQuery as a backend. All of the business/processing logic gets decoupled into PowerQuery, and formulas become primarily tools for presentation logic (such as adding an an Exceptions notice to the primary worksheet if COUNT(EXCEPTION_TABLE)>0). [1] https://www.howtoexcel.org/tips-and-tricks/11-awesome-examples-of-data-validation/ https://www.howtoexcel.org/tips-and-tricks/11-awesome-exampl...

by cosmie 6y ago

clairity 6y ago

> "1. Create table(s) for raw/user-inputted data" you can still be exposed to data munging on input/import, due to all sorts of formatting issues (like zip codes losing their leading zeroes or invalid phone numbers due to misaligned formatting).

cosmie 6y ago
Very true, and that's why I prefaced my comment with "only helpful to an extent". In some cases, data validation[1] on the cells directly can help. Particularly if the data is going to be manually entered by the end user, rather than a bulk copy/paste job from elsewhere. In other cases, you apply defensive checks within the PowerQuery processing. If a field is supposed to contain US-formatted phone numbers, you can explicitly set the column type to text, split at dashes and keep the first part, verify it only contains numerical content, optionally check for non-sense values (all zeros, all nines, 12345, etc), then left pad it with zeros to ensure they're all 5 digits. You can also package up all of those steps into a function that can easily be added into future worksheets via copy/paste and save you from re-creating the wheel. And depending on the data itself, it may be handy to call out to a third-party API for validation/normalization. To maintain sanity, you can also create a helper column for validation. If any of the columns can't be "safely" normalized or validated, you update this field with details on what failed. At the end, split out any records where that column is not null into its own table. That way you both filter those records out of the "final" table, and can also easily add in an Exceptions Report section to the spreadsheet, clearly calling out the rows that had issues and providing the error details to the end user. It sounds complicated, and it is compared to the alternative (Excel's undefined behavior silently swallowing it). But it's actually incredibly straightforward to implement and a fairly natural design pattern for someone coming from a software development background. You just start treating Excel as a UI and PowerQuery as a backend. All of the business/processing logic gets decoupled into PowerQuery, and formulas become primarily tools for presentation logic (such as adding an an Exceptions notice to the primary worksheet if COUNT(EXCEPTION_TABLE)>0). [1] https://www.howtoexcel.org/tips-and-tricks/11-awesome-examples-of-data-validation/ https://www.howtoexcel.org/tips-and-tricks/11-awesome-exampl...