Recently I was helping a client visualize their excel data using Power BI and ran across an error in Power BI when it couldn’t deal with “#DIV/0!” as a cell value in Excel:
“DataFormat.Error: Invalid cell value ‘#DIV/0!’.”
Excel error #DIV/0!
Power BI error when trying to read that excel file: “DataFormat.Error: Invalid cell value ‘#DIV/O!’.”
You get this error in excel whenever you have a calculated formula that is trying to divide by zero or a blank cell. Similarly, you get “DataFormat.Error: Invalid cell value ‘#VALUE!'” typically when your data formats don’t match – like there’s a word in a number-formatted column.
It’s a simple fix: just modify your Excel formula to be wrapped in =IFERROR((YourFormula),0) so that if your formula attempts to divide a value by zero, it returns a value of zero instead of an error code.
My local SharePoint Saturday, SPS Kansas City, is coming up THIS WEEKEND (#SPSKC). I’ll be speaking about one of my favorite topics – building a modern intranet:
Building the Intranet of the Future: Using SharePoint to Empower Collaboration
Automation, organization, incredible search capabilities and user interactivity; all things your organization can have in choosing to build an intranet with SharePoint. We’ll discuss avoiding File Dump 2.0 by exploring best practices in internal communications and collaboration via intranets. We’ll also cover rethinking forms and minimizing busy work by utilizing automation capabilities via workflow and designing intuitive navigation.
Having just passed both 70-346 and 70-347, I know how hard it can be to find helpful and relevant resources to help you prepare for the exams. I’ve compiled resources below I used to help me prepare and I hope they help you with your own studying. I’ve included nothing more or less than I used myself. The most important piece for me was the Lynda.com courses – I wouldn’t have passed without them. Good luck!
Be willing to learn and memorize some fundamental PowerShell for O365 apps. Both exams require knowing some cmdlets and structure. These are all useful and covered in the exams (from docs.microsoft.com):
Whenever a user or administrator has options (licenses, migration methods, deactivating certain features, adding different roles/permissions, etc.) learn each of them and when they’re applicable
There are sometimes multiple correct solutions, but it’s a matter of understanding which have the least amount of administrative overhead or are most appropriate in different environments under different requirements (for example, if you’re not allowed to connect to the internet, how would you migrate exchange to O365?)
I failed and regretted not buying the “replay” option. I didn’t make that mistake twice. When you schedule your exam, I’d highlyrecommend doing so with a replay option so you get an included retake at a lower price than two exams individually. You could, alternatively, purchase a practice exam which should also give you a good idea of what to expect.
Get to know allthe admin centers (Skype, Teams, StaffHub, SharePoint, etc.), their settings and how they allow or disallow external access
Use the exam pages to guide your studying, particularly in areas you’re uncomfortable with:
70-346 Skills Measured
70-347 Skills Measured
These are taken directly from the exam info pages. Clicking a link will take you to the respective section of the page to see specific sub-topics covered under each skill section. I’ve also included the “Preparation resources” provided by Microsoft under each respective section:
I’ll be speaking this Saturday at SPS Denver (aka SPS Colorado) (#SPSColo). I’ll be presenting a brand new, magical session on content management, archiving and retention:
SharePoint wizardry for content management, archiving & retention
Are your muggles muddying up your servers and site storage with duplicate files, pictures from a 2008 staff party and files named “April” in folders called “Jane’s stuff”? Learn how to most effectively destroy the 8th horcrux using out-of-the-box functionality in SharePoint brewed with bits of governance, content strategy and just a little help from SharePoint Designer to help automate processes. It’s just like magic!
A client of mine recently ran into an issue I hadn’t seen before. When she would click a formula cell and drag down to calculate it across multiple rows, it only copied the value. The formulas were correct, but the value being shown was from the original cell:
RELATED() pulls values from different tables based on relationships established in the relationships tab. This is useful and makes it so we don’t necessarily need to append or merge tables to get the same result.
For example, we can create a new calculated column in our fact table Sales that pulls in related information from two other tables without needing to append or merge tables:
By default when a user chooses to share a document, a selection has already been made to share that document with anonymous access, restricted organizational access or only with specific people (requiring entry of recipients) depending on who set up your tenant. If no changes were made, chances are the links default to anonymous access. A user can change this setting to restrict access before sharing but for newer users in a hurry, it’s likely anonymous links could be being created and shared incidentally because time isn’t being taken to review the alternative options.
Here’s how you, as an administrator, can change the default settings to something more secure without (or with) removing anonymous capabilities.
After you’ve made changes to managed metadata or the search schema you may wish to re-crawl your content sooner than the next scheduled crawl. You can do this across all site collections (on-prem/server), one site at a time (O365 or on-prem) or individual lists and libraries (O365 or on-prem).