Posted on

Add or subtract hours from date/time values in Power BI using M formula in Power Query Editor

Previously, I shared how to transform date/time values in Power BI using DAX. But I’m working on building more using M formulas as those are less resource-intensive and improve report performance.

The following was my solution for correcting a time zone issue in which the original data’s date/time column was 5 hours ahead.

=[#"Date/Time"] - #duration(0, 5, 0, 0)

That example subtracts 5 hours from a column called Date/Time. You can change the column name to your own, of course. Also update the numeric values in the #duration part to add or subtract days, hours, minutes, and seconds, respectively.

#duration(days, hours, minutes, seconds)

And just change the – to + to add instead of subtract.

Examples

Add seven days (a week)

=[#"Date/Time"] + #duration(7, 0, 0, 0)

Correct a time zone – Add hours

=[#"Date/Time"] + #duration(0, 5, 0, 0)

Correct a time zone – Subtract hours and minutes

=[#"Date/Time"] - #duration(0, 2, 30, 0)

Learn more about M formulas and functions

Check out “Add a custom column in Power BI Desktop” from Microsoft Docs to get started with creating custom columns using M formulas instead of DAX.

This Power Query M function reference guide is super helpful for learning more about available functions to use in your M formulas in Power Query Editor.

Posted on

Export SharePoint group membership names and email addresses to Excel

Before we start, if you’re just wanting to email all members of the group you can “select all” then choose “E-Mail Users” from the action menu when viewing the group membership within SharePoint. This opens an Outlook window with all the addresses pre-populated.

But, if you still need an excel sheet of membership for another purpose (perhaps to format a sign-in sheet, generate documents with mail merge, share the list with others, etc.) follow these steps:

  1. With the group open in SharePoint, copy the URL and note the ID number at the end of the URL
  2. Open a new excel workbook
  3. From the “Data” tab, select “New Query” –> “From Other Sources” –> “From OData Feed“
  4. Paste the group URL in the prompt but delete everything after the site address and replace with the following, replacing “6” with your group’s ID from step 1
    /_api/Web/SiteGroups/GetByID(6)/Users
  5. When prompted for login credentials you’ll use “Windows” for on-prem/server and “Organizational account” for O365.
  6. Click “Edit” once the group loads so we can choose which columns to keep/delete
  7. Ctrl+click the column headers you want to keep
  8. Right-click a header of a column you’re keeping and select “Remove Other Columns”
  9. Close and Load
  10. You should now see your group membership and email addresses (and any other fields you kept). Save this somewhere and, if updates are made in the future, just click “Refresh all” to bring in new members and remove old.

Updated 8/8/2019: Added step 5 to show the login credentials prompt.

Posted on

Power BI refresh error “could not load file or assembly…provided impersonation level is invalid”

Short version

Run Power BI Desktop as administrator before opening the report.

Explanation/Scenario

Recently, when opening a report from Power BI Report Server that used a SQL database as a data source I received the following error.

An error happened while reading data from the provider: ‘Could not load file or assembly ‘System.EnterpriseServices, Version=4.0.0.0, Culture=neutral,PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0X80070542)’

So I clicked “Edit Queries” to see what was going on and received almost the same error but referencing a specific table and getting me thinking about SQL specifically.

Error seen after clicking “Edit Queries”

So I checked that table in the query editor and received yet again the same error.

Error seen after clicking the specific table referenced in the previous error message

Simple solution. It’s likely that your credentials and queries are just fine.

The solution for my particular issue here, and the meaning behind the “impersonation level” part of the error, is just that you need to run Power BI Desktop as administrator before accessing the report. After opening the report in Power BI Desktop being ran as an administrator, everything worked as expected.

Posted on

Add a print button to SharePoint list items

One of my more popular requests is an easier way to print list items (either to PDF or paper). Using SharePoint Designer, we can add a “Print Item” button to the display forms for all list items. This button appears in the “Actions” section of modal dialogs AND standalone display forms in their own window. See above for an example of a modal dialog implementation.

You can do this rather quickly, but will need to be a site admin with access to SharePoint Designer to make the change.

Note: This only works for classic view lists.

  1. Download the print icon
  2. Add print icon to Site Assets
  3. Add a custom action to list

1. Download the print icon

2. Add print icon to Site Assets

  1. Open your site in SharePoint Designer
  2. Go to Site Assets and import the downloaded print icon (using import/browse OR drag-and-drop)

3. Add a custom action to list

  1. Open your list in SharePoint Designer
  2. Add new custom action for display form ribbon (upper left dropdown)
  3. Name it “Print Item”
  4. Set “Navigate to URL:” to 
    javascript:window.print()
  5. Browse for the button image URL (I used 32×32) and select the print.png image you added to Site Assets
  6. Change “Manage” in Ribbon Location to “Actions” and click OK

    Ribbon.ListForm.Display.Actions.Controls._children

Test away!

Troubleshooting

I don’t see the “Ribbon Location (Tab.Group ID:)” option!

You need to double-check step two of “Add a custom action to list.” You must create the action from this dropdown field and specifically select “Display form ribbon” to get this option.

It’s not showing up!

  1. Make sure you types “Actions” (plural) and not “Action”
  2. Refresh your SharePoint list (Ctrl+F5 for a hard refresh)
  3. Make sure you’re using the classic experience. This does not work in modern. You can accomplish this by forcing the classic experience for all users (recommended for consistency):

If you’d rather let users choose, just be sure to educate them on the ability to switch back and forth.

Posted on

Flow now supports multiple condition controls for advanced if/then scenarios within a single step

Say goodbye to nested if/then statements in Flow taking up fourteen monitor widths. Flow now supports nested if/then statements all in a single, vertical step. For example, the following requires that WeekDayNum is not 0 or 7 AND requires that either Bob or Nate created the item. And I didn’t have to scroll horizontally at all to see it!

The next time you use a condition control, enjoy rethinking how you might structure your various requirements for conditions to be met.

To get started, just add a condition control as you normally would:

  1. Add an action
  2. Condition control


Enjoy!

Posted on

MS-100 & MS-101 Exam Study Guides

Update 10/14/2019: My MS-101 exam guide is now available for pre-order. Also, the courses previously listed below are no longer available and have been removed.

If you’re pursuing the M365 Enterprise Administrator Expert certification, you’ll need to pass MS-100 and MS-101, as well as a prerequisite certification (see link for more info). The following study materials will help with preparing for these two exams.

Also, if you haven’t heard, the current MCSA exams 70-346 & 70-347 have retired so the exams below may very well be part of your new path:

Order exam prep books

My exam guide covers every MS-101 exam objective, helping you prepare for taking and passing the MS-101 exam. Includes:

  • Self-assessment example questions at the end of each chapter
  • Further reading recommendations for each objective
  • Exam tips throughout
  • Two 20-question mock exams with detailed answer explanations

Other exam prep books

Order the MS-100 exam prep book (release date May 6, 2019)

Order the MS-101 exam prep book (release date June 27, 2019)

Courses

  • Check LinkedIn Learning for MS-100 and MS-101 topics. You may have better luck searching for specific exam objectives than exam names.
  • Check Microsoft Learn for specific exam objectives.

Note: Previous OpenEdX courses have been archived and are not open for enrollment as of this post’s last update. If you had previously enrolled, you may review the archived course content by logging in.

Other blogs and pages

Ignite overview video


From Ignite 2018: Microsoft 365 Enterprise Admin role-based exam prep: MS-100 Identity & Services – BRK3385

Ready to take the exams?

  1. Buy exam voucher + replay (extra try if needed) on MindHub
  2. Schedule an exam:
Schedule MS-100
Schedule MS-101
Posted on

Add a new OneNote notebook to a SharePoint site

If your SharePoint site doesn’t currently have a notebook (most likely stored by default in “Site Assets”), you can add a new one in any of the following ways:

In a document library

This is the best option for end-users or site owners adding a new notebook at any time.

  1. Click the “New” button
  2. Select OneNote Notebook
  3. Name Notebook
  4. Click OK

Via Site Feature

This is a good option for administrators creating a new site for a team.

Note: Adds Notebook to Site Assets or default library, but not a particular document library. Also adds a link to Quick Launch.

  1. Site Settings –> Site Features
  2. Site Notebook –> Activate
  3. Notebook is automatically named after the site’s title (e.g. Site Title Notebook)

Moving a pre-existing notebook to SharePoint from a local location

See detailed instructions here.

  1. Select “file” in the upper left
  2. “Share to web or network location”
  3. Navigate to SharePoint library by URL within the move dialog
  4. Rename (if applicable) and click “Move”

By dragging and dropping from a local location or using the “Upload” button in a document library

While not an ideal method, it may sometimes be your only option.

Note: This makes a copy of the Notebook you’re adding – this will not be connected once added and you’ll have two copies of the Notebook (the copy and your original still in its local location).

  1. Drag Notebook from local location
  2. Drop into a document or asset library in SharePoint

Or

  1. Click “Upload”
  2. Navigate to and select the existing Notebook on your computer

If using this option, I recommend deleting/closing the Notebook from your local location, opening the copy from SharePoint and syncing that one so you don’t inadvertently make changes to the old copy.

Posted on

“Unable to connect” error when accessing SQL database in Power BI Desktop

When attempting to access a SQL server/database in Power BI, you might get the following error message:

Unable to connect

We encountered an error while trying to connect.

Details: “An error happened while reading data from the provider: ‘Could not load file or assembly ‘System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=…..

Error message received when trying to get a SQL database as a data source in Power BI

Solution

You just need to run Power BI as administrator before attempting to access the SQL database as a data source.

  1. Save your work and close Power BI Desktop
  2. Right click Power BI Desktop and select “Run as administrator”

3. Attempt to access your database again – you shouldn’t have an issue.

Posted on

Open a SharePoint Library in Windows Explorer and save it to Quick Access

If you can’t sync a library, or prefer not to, you can pin the web location of a SharePoint form, media, or document library to your “Quick Access” panel in Windows Explorer.

This allows you to interact with (edit, replace, delete) the files just as you would in a network drive but requires that you’re connected to your SharePoint environment – there are no offline files in this method.

Access the library in Windows Explorer

  1. Copy URL of library in SharePoint
  2. Open Windows Explorer and paste in URL, deleting everything after the library name
  3. Hit Enter

Pin to Quick Access

You could then right-click “Quick access” on the left of Windows Explorer and select “Pin current folder to Quick Access” to bookmark it for next time

Posted on

Microsoft Surface Pro 4 “plugged in, not charging”

Alas, I reached a day where I thought I was done with my Surface. It powered on when plugged in just fine but was at 0% battery and would, of course, instantly shut off when disconnected from AC power. When I hovered over the battery indicator in the task bar, it told me it was “plugged in, not charging.” The nerve!

After some troubleshooting and trying:

  • Making sure devices, particularly under “Batteries”, were all updated
  • Uninstalling and reinstalling power related devices via Device manager
  • Running anti-virus checks
  • Installing latest Windows updates

I found out that if I plugged my surface dock into the surface “upside down” it worked fine and charged as usual. So, long story short, if your Surface isn’t charging just try connecting the power source in the opposite direction.

Also, once it “remembers how to charge itself,” you can reverse this and put the charger in the normal direction (just double-check that it still says charging). Think of it as a quick “refresh” of the surface power connector.