How to count per-row occurrence of multiple words or phrases across multiple columns in Power BI

Sometimes you want to know how many times words and phrases appear across multiple columns. For example, if we want to know how many times “Cat” OR “Mouse” appears in each row, we would get:

Column 1 Column 2 Column 3 Word Count
Cat Yarn Mouse Cat Dog 3
Dog Cat Horse Hay Dog 1
Cat Cat Cat Mouse 4

Continue reading “How to count per-row occurrence of multiple words or phrases across multiple columns in Power BI”

How to get a free trial of Office 365 for home, business or enterprise

A free trial of Office 365 is a great idea if you’re:

  • Curious how it could help your team and want to do some testing as a group
  • Wanting hands-on experience with tools you’re interested in using
  • Needing to test something out in a safe, no-consequence zone before applying it to your actual environment
  • Studying for exams 70-346 or 70-347 (I recommend a Business Premium trial)
  • Considering changing licenses

Get a free trial today:

Continue reading “How to get a free trial of Office 365 for home, business or enterprise”

Solution: Excel drag to “fill” not working – value is copied, formula ignored

Note: Video of solution at bottom of post.

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:

Continue reading “Solution: Excel drag to “fill” not working – value is copied, formula ignored”

What is the Related() function in Power BI?

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:


Continue reading “What is the Related() function in Power BI?”

Change the default link type and sharing settings for SharePoint documents

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.

Continue reading “Change the default link type and sharing settings for SharePoint documents”

How to manually start crawls and reindex SharePoint content

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

Continue reading “How to manually start crawls and reindex SharePoint content”

Use Microsoft Stream to live stream your events

I just found out that Microsoft Stream now allows users to live stream events (preview). Once an event is finished, the recorded stream includes face detection so users can skip ahead to a certain speaker as well as automatic and searchable closed captions. Whenever you have an opportunity to stop paying for a third party product and use what you get included in your tenant, I think it’s worth giving it a try.

Note: Video tutorial at bottom of post.

Continue reading “Use Microsoft Stream to live stream your events”

Leading zeroes in Power BI: Restoring them from auto-detection of data types

Power BI tries to be helpful and auto-detects data formats such as dates, decimal numbers and integers. However if you’re dealing with zip codes, employee IDs, product IDs, etc. that have leading zeroes, you may not want them auto-converted to integers/number format which removes the leading zeroes. Here’s how to get them back.

From Power Query Editor (recommended)

  1. Click the “Data type” icon next to the column name
  2. Select “Text”

From Data Tab

  1. Select the column header
  2. From the Modeling tab, change “Data type” to “Text”


The ABCs of SharePoint: 26 ways SharePoint can enhance your digital workplace

Now a book!

Based on his 100th blog post, Nate Chamberlain presents “The ABCs of SharePoint: 26 ways SharePoint can enhance your digital workplace”. Can you think of something in SharePoint for every letter of the alphabet? This book will help you fill the gaps teach you more about things you could implement in your own existing SharePoint environments. From Automation to Zone Templates, Nate shares 26 features and topics that set SharePoint apart from any other collaborative platform.

Order Paperback                  Order E-Book

A | Automation

SharePoint Designer & Microsoft Flow allow for endless possibilities in automating business processes.

  • Microsoft Flow
    • Connect to external services like Twitter, Google or other site collections using Microsoft Flow
    • Complete approvals or start workflows with the tap of a button using Microsoft Flow’s app for mobile devices
  • SharePoint Designer
    • Work within a single site collection and perform advanced approval processes, metadata automation, permissions management and more using SharePoint Designer
    • Automatically generate new items and documents, send emails and more based on if/then statements you create
  • Calculated columns
    • Your lists and libraries already track who is creating, when they’re creating and information from users’ profiles. Add to this automatically-collected metadata by creating calculated columns to extract “Year” from date, sum multiple columns for a total, or display the duration until/since a date

B | Buttons & Iconography

Iconography is important in digital workplaces. Your users should, on any one page, be able to recognize where they are and what they can do easily by observing navigational elements (breadcrumbs, menus, etc.) and buttons/icons. Instead of simple text links, use buttons/images to give a more polished look that provides for quick visual recognition.

Check out Stacy Deere-Strole’s “Quick Wins: #4 – Promoted Links” to learn of a quick anyone-can-do-it method of adding icons/buttons to your pages using the Promoted Links app. You can also add a hyperlink to any image to create a single “button” experience.

C | Content Types

Content Types are essentially templates for lists and libraries. While your list may have 15 columns, content types allow you to show only certain fields conditionally. For example, a person may submit an item with 4 fields and a workflow can change the content type to an “Admin” content type which might “add” two additional fields for approval and comments.

In Document Libraries, content types allow attaching any office file as a template to each content type. This means when a user clicks “New” their options could be “Vacation Request”, “LOA Request”, “Time Off Request”, etc. And each content type could have its own separate workflow process, metadata, views, etc. But they all exist in one library.

D | Document Sets

Document sets are great for collecting many files that all share the same metadata. For example, when working with projects all documents associated with it would have the same client, vendor, organization, project number, project manager, contact info, etc. The document set works as a sort of folder, and all documents added to it then get the same metadata automatically. You can also set a document set to include certain templates or links by default so that upon creation, a document set already has all the forms, templates and folders it needs.

E | Embeds

SharePoint, especially SharePoint Online, allows for embedding all kinds of items in your pages:

  • YouTube Videos
  • Code
  • Power BI Reports
  • PowerApps forms
  • Other web pages (if the content is allowed in iframes)
  • Any SharePoint media content or files
  • See “W | Web Parts” below for more ideas of what could be embedded in your SharePoint pages

F | Forms

Whether a customized PowerApps form or an out-of-the-box SharePoint form, every list and library is able to give users a “form-like” experience for completing metadata. You could, for example, have a list for time off requests and use the default newform.aspx page or you could use SharePoint Designer (server/on-prem) or PowerApps (O365) to create custom new item forms. Each content type (see “C | Content Types” above) can have its own new item form as well.

The following is an example of a custom on-premise or classic form created with SharePoint Designer. See How to make a floating “attachments” block for SharePoint forms to see how it was done step-by-step.

G | Governance

Governance is an essential component of any digital workplace. Governance typically involves a group of individuals with different experience levels, and different departments/business processes. These individuals help guide decisions around appropriate usage and standards for your intranet and its content.

Check out How to create a SharePoint Governance Plan (includes template) for ideas on how your governance committee could get started in creating a more consistent, clean and user-friendly intranet with priority and focus on relevant content and its maintenance.

H | Hub Sites

“[Hub sites] are the ‘connective tissue’ you use when organizing families of team sites and communication sites together.”

Hub sites are relatively new and available in Office 365. Think of them as buckets in which you’ll organize your existing sites, or landing pages for groups of sites. Learn more here.


I | Intranets

I would venture to guess that intranets are the number one usage of SharePoint. With all the possibilities in automation, content management and strategy (including retention/archiving), communication and document collaboration it makes it a natural choice for companies needing more than just shared documents. And with Office 365, your intranet is everywhere with you thanks to the SharePoint mobile app and mobile compatibility.

Check out my presentation “Building the Intranet of the Future: Using SharePoint to Empower Collaboration” presented at SPS Omaha in 2018.

J | Javascript & JSON Customization

Being able to customize sites, pages or just lists with your own javascript and JSON is a must for many. On classic/on-premise sites, you can implement your own javascript and css. Check out this example of using javascript and jquery to create customized, floating headers for SharePoint list.

In the modern experience, you’ll need to learn about SPFx and can use JSON to do things like conditional column formatting.


K | Knowledge Management

411wNjZIZzL._SX387_BO1204203200_Knowledge management is just as important as the technology we use to contain it. If you haven’t read Content Strategy for the web, you need to. It’s one of my absolute favorite resources I turn to repeatedly. I was surprised at how ready SharePoint was to incorporate the practices and concepts in this book – it’s a perfect pair.

This book helped me recognize the importance of nurturing good content management practices as well as developing and maintaining efficient content managers. It clearly defines separate content and people concepts and leaves you with many ideas you can begin exploring or implementing right away to improve your organization’s knowledge management, no matter the scale. And it’s a fun read – it’s written with light humor which makes an otherwise ordinary topic an exceptional read.

So if you want better search, less frustrated users, relevant content, effective and active archiving and retention policies, a sense of content ownership and clearly defined roles this book will help you get there.

L | Lists & Libraries

Lists and libraries are the main, most frequently used, web parts (see W | Web Parts below) in SharePoint.

Lists function like spreadsheets, and can create a sort of “dashboard” where you use list views to display the most important data from each list item/form submission and then you can open the item to see full detail. You can attach documents to list items, but it’s not easy to later pull all those attachments or move them. A library might suit your process better if you’ll have lots of attachments.

Libraries contain documents (of all types) and are basically an upgraded version of lists. The emphasis is placed instead on the actual document to which you can assign metadata just the same. Thanks to co-authoring, workflows, versioning and item-level permissions, libraries in SharePoint are at the forefront of digital collaboration.

M | Metadata

Metadata is a love letter to your intranet’s future. By setting up metadata (additional columns in lists and libraries) you’re making it possible to:

  • Simply rearrange all documents or items in just a few clicks
  • Improve search
  • Perform workflows based on metadata values
  • Enable reporting with better filters, sorting and counts (using Power BI or exporting to Excel)
  • Eliminate the need for folders in favor of views
  • Create “lookup columns” of coordinating metadata from other lists for a database-like experience

N | Navigation

Navigation is critical to user-adoption. As mentioned in “B | Buttons & Iconography”, users should be able to recognize where they are, what they can do and where they can go from any page on your intranet. You can use a combination of visuals and menus to achieve this. Also consider placing emphasis on search in SharePoint – it’s perhaps the most efficient way to find what you’re looking for if maintained and set up correctly.

Make sure your menus are consistent. No matter what page a user is on, the navigation should be similar, if not identical, throughout. It’s nice to customize and personalize, but it’s nicer when a user knows just where to look for similar information across different departments.


O | Office 365 Integration

SharePoint integrates seamlessly with other O365 apps to create a one-stop shop experience. Whether embedding, building workflows or customizing content you’re likely to experience any of the following apps within your SharePoint environment:

P | Permissions

In SharePoint you can use item-level permissions, site or object-based permissions or any combination of the two. But of course it’s best practice to minimize the breaking of inheritance and try to manage permissions on a group basis rather than individuals.

For example, at LMH we manage our security groups in AD rather than SharePoint so that when people leave, we don’t need to remember to remove them from multiple SharePoint groups as well since they’re already being deactivated in AD. But find what works best for you and your sysadmins and make sure you’re on the same page in routine management of those groups.

One of my favorite things about SharePoint is how it’s security trimmed so that people won’t even see links to sites or find search results to things they don’t have permission to access. Everyone in an organization could, in theory, have a completely custom and different experience thanks to search, permissions and the office graph.

Q | Queries & Search

As mentioned before, search could well be the best way to navigate in SharePoint. But aside from search, you can also use the content search and content query web parts to search for certain content across your collection(s) and display them in a block on any page. For example, maybe you want to aggregate all calendar items from multiple sites into one list on your home page. Or you could link a button to the most recent document uploaded to a library, such as always linking to the current lunch menu.

You can also customize search and build your own search results display template, filters, etc. and limit the scope of a search. For example, I’ve built mini-search pages for video libraries to give departments a YouTube-like experience which only searches their videos and has custom search refinement panel on the left with specific metadata to filter results.

R | Roadmap

Stay up-to-date on what’s been released, what’s coming soon and what’s in development by watching the SharePoint Roadmap.

S | SharePoint Saturdays & Conferences

There are many free opportunities for professional development near you. SharePoint Saturdays bring in industry experts, including many MVPs, MCTs, etc., to share what they know and love. Learn from the pros, at no cost to you, and take home some new knowledge (and swag).

If you have a bit of a budget, check out Microsoft Ignite, SharePoint Fest, or SPTechCon.

Matt Wade has done a phenomenal job listing many of the upcoming SharePoint events (free SPS events as well as paid registration conferences) on his site. You can subscribe to his calendar or download the listing here. You can see me at these upcoming events.

T | Tasks & Timelines

One of my largest use-cases is project management in SharePoint. People are very interested in task lists that can email you and remind you when things are overdue.

Workflows in SharePoint Designer can assign tasks which are posted to the Workflow Tasks or Tasks list in SharePoint. You can set overdue reminders on these as well in SharePoint Designer that send at a regular interval until completed. Those assigned can also reassign tasks.

Even without workflow, you can have any number of task lists you manage in SharePoint and associate that list with a timeline. It’s an easy way to share with your team what’s going on and who is responsible for what. Pair a list with a timeline or Gantt chart for an added “wow” factor.

U | Usage Reports

Analytics are incredibly important for measuring the success (or lack thereof) of content and communication. It’s also nice to understand popularity trends. Out of the box, SharePoint has some usage and search query reports (site settings –> popularity and search reports) but you’ll want to pair this with Google Analytics or another analytics service to get more robust insights on user trends and behaviors.

V | Versioning

By activating versioning, you’re allowing users to see what changes have been made to metadata, compare the current version of an item or document to a previous version, or restore a previous version. You can also allow both major (published) and minor (draft) versioning.
version history

W | Web Parts

Web parts are the building blocks of SharePoint pages. These can be static or dynamic, for display or for interaction. Depending on whether your page is modern or classic, you have different options:

Modern web parts:

Classic web parts:

X | XML and XSL

You might be editing XML and XSL if you’re on-premise or using server and attempting to change a look or layout the old fashioned way. If online/O365 you’ll likely be modifying the SharePoint Framework (SPFx) instead.

Y | Yes/No Functionality

  • The default “yes/no checkbox” option as a column type is great for placing boxes on forms for quick checking
  • Use a choice field with Yes and No as options and a bit of javascript to conditionally display fields based on the response
  • Build if/then workflows in SharePoint Designer or Microsoft Flow that move to different stages/steps based on a user’s response to yes/no prompts

Z | Zone templates

If on-premise/server, you may be creating custom layouts using zone templates in SharePoint Designer, or just wanting to learn more about web part zones.

How to increase lookup column limit in SharePoint lists

Lookup columns include workflow status columns, traditional lookup columns to other lists, and person/group columns. This includes the two default people fields “Created by” and “Modified by”. If your list has more than 12 of these, you may receive the following error:

“This view cannot be displayed because the number of lookup and workflow status columns it contains exceeds the threshold (12) enforced by the administrator.”

list view threshold.PNGIn SharePoint Online, you’re not able to increase the lookup column limit. Lists created prior to the June 2013 CU update are capped at 8 lookup columns, while those afterward are allowed 12.

However, on-premise SharePoint (server) allows you to change this limit to your heart’s content.

Continue reading “How to increase lookup column limit in SharePoint lists”