Three Ways to Load SharePoint Data into Power BI and Excel

Power BI includes a connector/wizard for SharePoint lists that makes is easy to load list content. It works great, but has a shortcoming or two. It first retrieves a list of all of the lists, and then it retrieves all of the list content… even if you only need a small subset of the data.

In this little article I'll show these approaches:

• Using the Wizards
• Writing your own "M" formulas
• Using the SharePoint ODATA RESTful web services, for lists and a lot more!

While they work for Power BI, they also work for Excel Power Query.

Using the Wizards

The wizards will write "M" formulas for you to connect to the SharePoint list. Although this will let you transform the data any way you like, all of the list content will be downloaded into Power Bi and then filtered.

1. Launch Power BI Desktop
2. Click Get Data from the ribbon
3. If the full list of connectors is not displayed, click More.
4. Scroll, or use the search box, to find and click the “SharePoint Online List” or "SharePoint List" connector.
5. Enter the Site URL. (Just the URL to the site, not to a page or a list.)
   Example: https://yourDomain.sharepoint.com/sites/yourSite or https://yourDomain.sharepoint.com/sites/yourSite/yourSubSite
6. Click OK.
7. Find and checkmark your SharePoint list.
8. Review your data and click Edit. (SharePoint adds a number of hidden/internal columns that you will probably want to exclude.)
9. Optional: In the Query Settings area, click in the Name box and enter a new name for the imported data.
10. Select all of the columns that you do not need and click Remove Columns. Or, select the columns you want to keep, click the dropdown under Remove Columns, and click Remove Other Columns.
11. Click Close & Apply.
12. Create your visuals!

Writing your own "M" formulas

The wizards just write formulas for you. With a little practice, you can write better ones! Although this will let you transform the data any way you like, all of the list content will still be downloaded into Power Bi and then filtered. (i.e. keep reading to see how to use the REST web services to download only what you need.)

1. Launch Power BI Desktop
2. Click Get Data from the ribbon
3. Click Blank Query. This will open the Query Editor.
4. Click the Advanced Editor button.
5. Write your formula, save your changes.
6. You can continue customizing the data in the Query Editor.
7. Click Close & Apply.
8. Create your visuals!

In the blank query you will see something like this:

let
    Source = ""
in
    Source

When you run the wizard in the previous example, it writes a formula similar to this:

let
    Source = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]),
    #"a1c236c1-7d6d-4141-9d25-bf6f8e25d8a5" = Source{[Id="a1c236c1-7d6d-4141-9d25-bf6f8e25d8a5"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"a1c236c1-7d6d-4141-9d25-bf6f8e25d8a5",{{"ID", "ID.1"}})
in
    #"Renamed Columns"

You can write a better formula than the wizard!

The "let" selects and restructures the data while the "in" is the final result where we get the data for the model. The "let" contains a series of functions that drill down, layer by layer, to get to the data you need.

The "Source" is a variable and can have any name. This first line selects the data source, SQL, SharePoint, etc. While the documentation for SharePoint.Tables says it uses a URL to the list, it is actually the URL to the server. (Kind of like how a SQL connection string names a database, but not a table.)

Here's an example of a hand written "let" statement that uses friendly names instead of GUIDs for tables and obvious names for the variables:

let
    SharePointSite = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]),
    ToyList = SharePointSite{[Title="Toys"]},
    Toys = ToyList[Items]
in
    Toys

These functions can be chained, so if you prefer a one line version:

let
    Toys = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]){[Title="Toys"]}[Items]
in
    Toys

And if you want to pick just the columns you need without a lot of clicking…

let
    Toys = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]){[Title="Toys"]}[Items],
    ToysData = Table.SelectColumns(Toys,{"Id", "Title", "Price", "Modified"})
 in
    ToysData

More on M:
    https://docs.microsoft.com/en-us/powerquery-m/power-query-m-reference

Ok, that's good to know… but we still bring back all of the rows of data, even if we just want a few.

Using the SharePoint ODATA RESTful Web Services

The SharePoint List connector lets you bring in data from SharePoint lists and libraries. Using the SharePoint REST API web services you can bring in just about any kind of data found in SharePoint, including list data, lists of lists, lists of users, lists of sites and much more. Even better, you can write filters and select columns so only the needed data is sent from SharePoint to Power BI. (And with other REST APIs you can bring in just about any data from Office 365!)

1. Launch Power BI Desktop
2. Click Get Data from the ribbon
3. Click OData Feed.
4. Enter a URL to a SharePoint web service. This one retrieves all Touring bikes.
   This example assumes a list named “Bikes” where we only want three columns and only the rows for "Touring" bikes.
   Example:
   https://yourServer/sites/yourSite/_api/Lists/GetByTitle('Bikes')/Items?$select=price,color,style&$filter=category eq ' Touring'
5. Click OK.
6. Enter credentials, if needed.
7. Click OK.  (You have already selected your columns and rows from the REST query, so no additional Power Query edits needed.)
8. Create your visuals!

Note: You could start with a Blank Query and enter something like this:

let
    Source = OData.Feed("https://yourDomain.sharepoint.com/sites/yourSite/_api/web/lists/getbytitle('Bikes')/Items?$select=Title,Bike_x0020_Type,Size,Retail", null, [Implementation="2.0"])
in
    Source

There are REST Web Services for Everything!

A few other ideas: (and even more ideas here: https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/get-to-know-the-sharepoint-rest-service)

• Get a list of all of the lists in a site:
  https://yourServer/sites/yourSite/_api/web/lists
• Get a list of all items in a site’s recycle bin:
  https://yourServer/sites/yourSite/_api/web/Recyclebin
• Get a list of all subsites in the current Site Collection:
  https://yourServer/sites/yourSite/_api/web/webs
• Get a list of all site users:
  https://yourServer/sites/yourSite/_api/web/siteusers?$filter=startswith(LoginName,'i:0%23.f')

You can load multiple REST queries, and then define relationships to build reports for all kinds of SharePoint activity!

 

Learning the SharePoint REST API

To experiment with SharePoint REST services you can add my SharePoint REST Tester to your SharePoint site. See details here:

   https://techtrainingnotes.blogspot.com/2017/04/a-sharepoint-rest-api-tester-with-ajax.html

and download from here:

   https://github.com/microsmith/SharePointRESTtester

SharePoint Validation Formula Tip – Don't Use IF!

I often see questions about SharePoint validation formulas in the online forums that include IF statements to return True or False. Something like this:

    =IF(  someCondition , True, False  )
    =IF( Amount > 100, True, False )
    =IF( AND( State = "OH", Amount>100 ), True, False )

The IF is simply not needed!

Simply replace this:
    =IF( Amount > 100, True, False )
With this:
    =Amount > 100

It's either greater than, or it is not. The greater than test returns a True or False all by itself. For that matter, the expression in the first parameter of an IF statement must return True or False!

Here's what it looks like in both Classic and Modern UI.

What if you need to reverse the True and the False result?

Use the NOT function to reverse the True/False value.
    =NOT( Amount > 100 )

ANDs and ORs Return True or False

Simply replace this:
    =IF( AND( State = "OH", Amount>100 ), True, False )
With this:
    =AND( State = "OH", Amount>100 )

Two more examples…

If the state must be within the tri-state area you could write:
    =IF( State="OH", True, IF( State="KY", True", IF( State="IN",True, False ) ) )
Or you could just write:
    =OR( State="OH", State="KY", State="IN" )

While there are examples that require IF, you can solve most validations with a simple comparison, or the with the use of AND, OR and NOT.

    =IF( AND( Amount>100, IF( State="OH", True, IF( State="KY", True", IF( State="IN",True, False ) ) ) ), True, False)

Yup… I have seen those. And, it can be replaced with:
    =AND( Amount>100, OR( State="OH", State="KY", State="IN" ) )


And… book plug here… I have a lot more on validation formulas in my book!

SharePoint Calculated Columns and Validation Formulas – The Book!

Available on Amazon!

It only took me a year… and it grew from a little booklet to over 200 pages… but its finally done!

Bought the book? Post any questions, bugs, typos and suggestions to this blog post!

Everything you need to know about SharePoint formulas for SharePoint 2010 to 2019 and SharePoint Online / Office 365!

This book is for the SharePoint “power user” who needs a better understanding of how SharePoint formulas work, where they don’t work, and how they are created. While at first glance SharePoint formulas appear to be simple calculations, they are both quite powerful and have weird limitations. In this book we will explore the basics of creating Calculated Columns and Validation formulas with some boring details, and over one hundred examples. We also explore workarounds for many of the limitations by using SharePoint Designer workflows and a few tricks!

 

Over 100 Examples!

A how-to book of formulas would not be too useful without a few examples. I've been collecting these for years. They've come from classroom questions, forum questions, and my own SharePoint sites. Now they are all in one place…

• · Over 60 Calculated Columns examples
• · Over 30 Column Validation examples
• · 11 List/Library Item Validation examples
  (and every one of the Column Validation examples can be used here.)
• · 7 Calculated Column Default Values examples
• · 15 Workflow “workarounds” for things SharePoint formulas can’t do

Scroll on down for a complete list of the examples.

Who is this book for?

Anyone who creates and customizes lists and libraries. These include: SharePoint on premise Farm Administrators, Office 365 SharePoint administrators, Site Collection Administrators, Site Owners, Power users, and Developers.

 

Where are formulas used in SharePoint?

• Calculated column formulas derive data from other columns in the same list item. These can be simple calculations and text manipulations, or complex Excel-style formulas.
• Column validation formulas are used to create custom validations for user entered data. These can be used to make sure that "quantities" are not negative and vacation requests are for dates in the future.
• Column default formulas, while very limited, can auto-fill columns with a date 30 days in the future or a message based on the month or day of the week the item was added.
• List / Library validation formulas are used to check the values from multiple columns to verify the relationship between two or more columns. Examples include making sure a task start date is before the task end date, or to make sure an entry has a "price per pound" or a "price each", but not both.

Workflow Workarounds?

SharePoint formulas only work with a few column types while SharePoint workflows can access just about any column type. So, with a little workflow work your formulas can make these columns available to SharePoint formulas. There's over fifty pages of workflow workaround tips:

• Workaround for People and Groups
• Getting Data from a Person or Group Column
• Getting a User’s Manager
• Workaround for the SUBSTITUTE Function
• Workaround for Getting View Totals from Calculated Columns.
• Workaround for Adding Images to a Column
• Workaround for Multiple Lines of Text
• Workaround for Lookup Columns
• Lookup a Single Item (No checkboxes)
• Extract Several Lookup Columns into a Single Line of Text
• Workaround for Multivalued Choice Columns
• Counting Items Selected in a Multivalued Column
• Workaround for Managed Metadata columns
• Workaround for Single Valued Managed Metadata Columns
• Workaround for Multi Valued Managed Metadata Columns
• Workarounds for Attachments

 

Table of Contents

• Read Me First
• Tips for Formulas
• Calculated Columns
• Calculated Column Examples
• Calculated Default Columns
• Column Validation Formulas
• Column Validation Examples
• List/Library Item Validation
• List/Library Item Validation Examples
• Other Forms of Validation
• Workflow Workarounds
• Error Messages

Examples:

Calculated Column Examples:·

·        Fun stuff that may not work for you:

o   Add HTML to a Calculated Column

o   Add icons or pictures to a Calculated Column

o   Building an Address (String Concatenation)

o   Create a Bar Chart Column

·        Columns for Views

o   View Filtering on a Calculated Column

o   Group by Year

o   Group by Month and Year

o   Group by Year Plus Month

o   Grouping on an Algorithm

·        Numbers

o   Adding Leading Zeros to a Number

o   Scientific Notation

o   Roman Numerals

·        The IF Function and Boolean Logic

o   Calculating a Discount using Nested IFs

o   Working Around Nested IF Limits

o   Convert from State Codes to State Names

o   ANDs and ORs - Approve if all approved, or reject if any one rejects

·        Test for values

o   Testing for a Range of Dates

o   Testing for Empty Columns

o   Testing for Errors

·        Summing and Counting Columns

o   Counting Yes/No Columns

o   Average

o   MIN and MAX

·        More on Numbers

o   Raise a Number to a Power

o   Rounding Numbers

·        Working with Text

o   Combining Text Columns

o   Display First Name and Last Name in a Single Column

o   Creating Title Capitalization

·        Return the Right Data Type!

·        Dates

o   Subtracting Dates

o   Finding a Date One Year in the Future

o   Change Date Formatting

o   Fiscal Year

o   Week Number

o   Day of the Year

o   First Day of the Current Month

o   First Day of the Next Month

o   Last Day of the Current Month

o   Last Day of the Previous Month

o   Last Day of the Next Month

o   Third Tuesday of a Month

o   Skipping Weekend Days

o   Next Workday “X” Days in the Future

o   Simple Solutions for 5, 10, 15 Working Days in the Future

o   Solution for Any Number of Working Days in the Future

o   Working Days

o   Number of Working Hours Between Two Dates

·        Formatting and Conversions

o   Formatting Numbers

o   Adding Text to Numbers

o   Adding Special Symbols (¥, £, etc.) to TEXT() Number Formats

o   Converting Numbers to Fractions

o   Financial Calculations

·        Random Numbers and Messages

o   Creating Random Numbers (Using NOW)

o   Creating Random Messages (using CHOOSE)

·        A Calculated Task Status

·        Great Circle Distance from Longitude and Latitude

·        Simplify a Workflow by Using a Calculated Column

Column Validation Examples

·        Limit time for user updates.

·        Boolean operations

o   Examples Using “OR”

o   Examples Using “AND”

o   Examples Using “AND” and “OR”

o   Examples Using “NOT”

·        Testing for Empty Columns

o   Yes/No

o   Dates

o   ISBLANK()

·        Working with Dates

o   Date Must be in the Future

o   Date Must be in the Future “x” Days

o   Date Must be in the Future “x” Days and not on a Weekend

o   Test Against a Specified Date

o   Date Must be Between Now and the End of the Current Year

o   Date Must be Within the Next 30 days

o   Date Must be the Last Day of the Month

o   Date Must be the First Day of the Month

o   Date Must be the Third Tuesday of the Month

o   Date Can Only be a Weekday

o   Date Can Only be Monday or Wednesday

o   Entered Date Must be for this Year

o   Date Must be Current Or Next Month Only

·        Working with Numbers

o   Testing for Numbers in Custom Increments

o   Limit the Number of Decimal Places Entered

·        Working with Text

o   Testing for Text Length

o   Testing for a Valid State/Province (or other code)

o   Test to Require an Uppercase or Lowercase Letter 

·        Examples Using Pattern Matching

o   Must start with an "a" or "A" and the third character must be a "c" or "C"

o   Match a Phone Number Pattern of xxx-xxx-xxxx

o   Match a Phone Number Pattern of xxx-xxx-xxxx and Limit the Length

o   Match a Phone Number and Make Sure Only Digits Have Been Used

And many more!

The Mysterious Semicolon in SharePoint Search

Discoveries:

• A semicolon in a search is the same as the keyword "AND".
• You cannot uniquely find text that contains a semicolon. (such as ABC;DEF)
• A semicolon used as data in a checkbox enabled Choice column causes data and search problems.

If you have more info, or know of an escape character for “;” please post a note to this article!

";" equals "AND"?

If I were to search a SharePoint list for "111 AND 222", I would find items where "111" might be in one column and "222" might be in another, or both are two words in the same column. I was trying to find data that looked like this, "111;222", and was getting wrong matches. At first I thought the semicolon was behaving as a wildcard, and that would have been really cool as SharePoint does not support anything other than an "*" at the end of a word.

From testing, it appears that a semicolon is identical to the keyword "AND". In the example below, note that all of the sample text entries are using "word delimiters" that SharePoint recognizes as search word breakers. As "data" a semicolon is treated as "white space" or a word breaker, and is ignored by search!

Here is a search for "111" AND "44" and note that no items were found.

You can't uniquely search for a semicolon!

If your data contains symbols in the middle of text, you will have a problem when searching for text that contains a semicolon. In the example below, note that the search for "555;777" also finds other items.

The addition of quotes will solve this problem for other special characters, but not the semicolon.

Semicolons and Choice Columns

A discussion of the semicolon would not be complete if we don't talk about what it does to a Choice column!

Consider Choice column with these choices: (third choice has a semicolon)

And for this Choice column we selected Checkboxes.

Now let's select some data. Notice the display when I select two items in a QuickEdit view. The semicolon is part of one item's data, and is also there as a delimiter between items.

When I click off of that row, note that the two selected items are being treated as three items!

When I attempt to edit that column again, the item with the semicolons has been lost because there are no "aaa" or "bbb" items in the list.

So…

• Don't put semicolons in your data if you plan to search for it.
• Don't put semicolons in multi-choice (Checkbox) columns.

Numbers are Being Added to My SharePoint List Internal Names

When you create a list or library, the name you enter becomes both the internal name (used in the url), and the display name. When you rename a list, only the display name is changed. If you later create a new list with the same name as a renamed list’s original name, the new list’s internal name will have a number added.

Here are the steps to show what's happening:

1. Create a new Custom list and name it "TestList".
2. Navigate to the list and note that the URL contains "TestList".
3. Go to the list and List Settings and use "List name, description and navigation" to rename it to something like "TestListNorth".
4. Note the URL. It's still "TestList".
5. Create a new Custom list and name it "TestList".
6. Navigate to the list and note that the URL contains "TestList1". This is also the internal name. The display name is "TestList".
7. Change the display name of this list to "TestListEast" and note that the URL is still "TestList1".
8. Create yet another new Custom list and name it "TestList".
9. Navigate to the list and note that the URL contains "TestList2". This is also the internal name. The display name is "TestList".
10. Change the display name of this list to "TestListWest" and note that the URL is still "TestList2".

The internal name is both unique and not changeable from the browser user interface. The display name is also unique amongst the display names, but can be different than the internal name.

Keep in mind that the deletion of large objects in SharePoint is a gradual and background process. You might get numbers added to the internal name when you delete a large list, or even a Site or Site Collection, and then recreate those objects and lists using the same names.

SharePoint 2016 Durable Links

I recently had a question in class about “Durable Links”. I did a search of the Microsoft sites to find anything official on SharePoint 2016 “Durable Links”, and basically only found a beta vintage blog article.

https://blogs.technet.microsoft.com/wbaer/2015/09/22/durable-links-in-sharepoint-server-2016-it-preview/

While I did find a number of other blog articles from the beta period, mostly of the “what’s new in SharePoint 2016” type, I found no TechNet articles. So… I thought I’d share part of one of my courses that has a section on Durable Links. This course is available from many Microsoft Learning partners, and of course, from MAX!

From:
Course 55198A: Microsoft SharePoint Server Content Management for SharePoint 2013 and 2016

SharePoint 2016 Durable Links

Prior to SharePoint 2016, renaming or moving a file would break all of the links and shortcuts that pointed to the file. In SharePoint 2013 you might have had a file named “FinancialStatementFY14Q2.xlsx” that had no spaces in the name. This is both an ugly filename and a name that will cause problems with search. (Users searching for “Statement” or “FY14” would never find it based on the title.) The 2013 URL would look something like this:

http://yourServer/sites/yourSite/Shared%20Documents/FinancialStatementFY14Q2.xlsx

Renaming this file to include spaces in the name would create the following URL. But, users with links to the old file will no longer be able to find it.

http://yourServer/sites/yourSite/Shared%20Documents/Financial Statement FY14 Q2.xlsx

Note the spaces in the URL will be replaced with “%20”.

Durable Links
SharePoint 2016 now appends a “d” query string to the URL with a unique ID that will not change even if the file has been renamed. (But not always… see notes below…)

http://yourServer/sites/yourSite/Shared%20Documents/FinancialStatementFY14Q2.xlsx?d=w780e689061e44dbfb4123fe450f4b957

After renaming, SharePoint will still find the correct document as it looks for the Durable Link ID first to find the document.

http://yourServer/sites/yourSite/Shared%20Documents/Financial%20Statement%20FY14%20Q2.xlsx?d=w780e689061e44dbfb4123fe450f4b957

To find the Durable Link in SharePoint 2016, or the SharePoint Online “Classic UI”, click the “…” next to the filename.

Notes:

• Durable Links are not a feature and cannot be enabled or disabled.
• Durable Links require Office Online Server to be part of the farm.
• Works with Office documents like Word, Excel and PowerPoint (i.e. things displayed in Office Server), but not other files like .jpeg, .png, etc.
• At the time of this writing, the Office 365 / SharePoint Online “Modern Library” pages do not offer a way to copy the URL that includes the Durable Link query string.
• Documents that are moved (drag and drop or cut/paste) will preserve the Durable Link ID. Documents that are copied and pasted will get a new Durable Link ID.
• The Publishing site Content and Structure feature does not preserve the SharePoint 2016 Durable Link. (A new ID is assigned after a Move.)

  .

No More HTML in SharePoint Calculated Columns!

Update: Here's a workaround using a workflow: http://techtrainingnotes.blogspot.com/2018/01/adding-html-to-sharepoint-columns-color.html


Just in case you missed it:

“Some users have added HTML markup or script elements to calculated fields. This is an undocumented use of the feature, and we will block the execution of custom markup in calculated fields in SharePoint Online from June 13, 2017 onwards. The June 2017 Public Update (PU) and later PUs will make blocking a configurable option for on-premises use in SharePoint Server 2016 and SharePoint Server 2013.”

https://support.microsoft.com/en-us/help/4032106/handling-html-markup-in-sharepoint-calculated-fields

So, no more of this:
  
Or this:
    =REPT("<img src='http://yourPath/yourImage.GIF' style='border-style:none'/>",[Value])
 

Topic Mapping for Course 20347 to Microsoft Certification Exams 70-346 and 70-347

(updated 10/25/18)

Microsoft’s “20347A: Enabling and Managing Office 365” course is a little different in that it dpes not have a one-to-one mapping to a single Microsoft exam. For example, while course 20483 maps to exam 70-483, 20347 maps to two exams: 70-346 and 70-347.
Here’s a guide for my students who are working on the 70-346 and 70-347 exams…

The course:

• 20347A: Enabling and Managing Office 365
  (Of course, available from MAX Technical Training from yours truly!)

The exams:

• 70-346 - Managing Office 365 Identities and Requirements
• 70-347 - Enabling Office 365 Services

---

For details of each topic see Microsoft’s exam pages:

• 70-346 - Managing Office 365 Identities and Requirements
• 70-347 - Enabling Office 365 Services

Exam 70-346

Plan and implement networking and security in Office 365 (15–20%)

• 20347 Module 1 – Configure DNS records for services (and the DNS info in the Exchange and Skype modules)
• 20347 Module 3 – Enable client connectivity to Office 365
• 20347 Module 11 – Administer Microsoft Azure Rights Management Service (RMS)
• 20347 Module 2 – Manage administrator roles in Office 365

Manage cloud identities (15–20%)

• 20347 Module 2 – Configure password management
• 20347 Module 2 – Manage user and security groups
• 20347 Module 2 – Manage cloud identities with Windows PowerShell

Implement and manage identities by using Azure AD Connect (15–20%)

• 20347 Module 4 – Prepare on-premises Active Directory for Azure AD Connect
• 20347 Module 4 – Set up Azure AD Connect tool
• 20347 Module 4 – Manage Active Directory users and groups with Azure AD Connect in place

Implement and manage federated identities for single sign-on (SSO) (15–20%)

• 20347 Module 13 – Plan requirements for Active Directory Federation Services (AD FS)
• 20347 Module 13 – Install and manage AD FS servers
• 20347 Module 13 – Install and manage WAP servers (Web Application Proxy)

Monitor and troubleshoot Office 365 availability and usage (15–20%)

• 20347 Module 12 – Analyze reports
• 20347 Module 12 – Monitor service health
• 20347 Module 12 – Isolate service interruption

Exam 70-347

Manage clients and end-user devices (20–25%)

• 20347 Module 5 – Manage user-driven client deployments
• 20347 Module 5 – Manage IT deployments of Office 365 ProPlus
• 20347 Module 5 – Set up telemetry and reporting
• 20347 Module 5 – Plan for Office clients

Provision SharePoint Online site collections (20–25%)

• 20347 Module 9 – Configure external user sharing
• 20347 Module 9 – Create SharePoint site collection
• 20347 Module 9 – Plan a collaboration solution

Configure Exchange Online and Skype for Business Online for end users (20–25%)

• 20347 Module 6 – Configure additional email addresses for users
• 20347 Module 6 – Create and manage external contacts, resources, and groups
• 20347 Module 11 – Configure personal archive policies
• 20347 Module 8 – Configure Skype for Business Online end-user communication settings

Plan for Exchange Online and Skype for Business Online (20–25%)

• 20347 Module 7 – Manage antimalware and anti-spam policies
• 20347 Module 7 – Recommend a mailbox migration strategy
• 20347 Module 11 – Plan for Exchange Online
• 20347 Module 7 – Manage Skype for Business

Configure and Secure Office 365 services (20–25%)

• 20347 Module 10 – Implement Microsoft Teams
• 20347 Module 10 – Configure and manage OneDrive for Business
• (not covered in 20347!) – Implement Microsoft Flow and PowerApps
• 20347 Module 10 – Configure and manage Microsoft StaffHub
• 20347 Module 11 – Configure security and governance for Office 365 services

.

Office 365 / SharePoint Online – Where’s Site Settings This Week?

SharePoint Online is kind of like the weather in Cincinnati… if you don’t like it, hang around, it will change.

Where’s Site Settings?

It depends… today, 10/23/2017 it’s here:

• Classic UI pages: It’s where it has always been… in the Settings (gear) menu.
• Modern UI pages: It’s…  Settings (gear), Site Info, Site Information, and then at the bottom of the popup panel, click “View all site settings”.
• Modern UI Site Contents page: Top right corner, click Settings

I wonder will it will be next week?

Purpose of the change?

No real clue… If it was to surface the most often used Site Settings, they are not the ones I use most often. Are the most often used options on your list Delete Site, rename the site or change the description or icon.

SharePoint Date Search Tips

 

Applies to SharePoint 2013 and later.

 

A few SharePoint Search Tips!

 

Time Zone

Search internally stores dates in Universal Time. Because of this, a file uploaded at “2/7/2017 10:50 PM EST” will not be found with a search for “Write=2/7/2017” (or “LastUpdateDate=2/7/2017”). That file will be found with a search using “Write=2/8/2017”.

 

Date Ranges

You can create searches on date ranges using “..”.

      Example: write=2/1/2017..2/8/2017

 

Named Date Ranges

You can also use the names of some date ranges. Quotes are required around any range name that includes a space.

     Example: write="this week"

   

The supported ranges are:

• today
• yesterday
• this week
• this month
• last month
• this year
• last year

But sadly… no “"last week”!

 

Comparison Operators

Note: All of the following operators work with the DateTime, Integer, Decimal and Double data types.

Operator
	Equals
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to
<>	Not equal to
..	Range

 

.