Data gateways allow users to connect online services, such as Power BI service, Power Automate, and Power Apps to on-prem data sources such as SQL databases, SharePoint server lists and libraries, and network shares.
As you can imagine, you wouldn’t want everyone installing their own individual gateways throughout your organization. Managing and sharing those centrally is much more efficient (and secure). You can manage who is allowed via the Power Platform admin center at admin.powerplatform.microsoft.com.
Note: You must be one of these roles to restrict gateway installers:
Azure AD Global administrator
Office 365 Global admin
Power BI service administrator
Restricting installations does not impact gateway administration. You can assign and re-assign users to administer and use gateways at any time. The following steps are strictly to manage who is able to install an enterprise gateway on a machine.
A simple trick in Excel allows you to remove duplicate values from a column in Excel. Depending on your version of Excel, yours may look a bit differently but the process is nearly the same. Below, I’m using Excel 2016.
1. Select the column, or the values from which you’re removing duplicates. Note: You could also just start with step 2, and then select the relevant column(s) from the resulting dialog. 2. Go to Data > Remove duplicates (in the Data Tools panel)
3. Confirm if your column has a header (column title or not) and click OK.
4. Excel will confirm the number of duplicates found and how many remain. Click OK.
The following is a DAX formula you can use to create a calculated column that shows “next year’s” value in “this year’s” row. You can easily adapt this to show “yesterday’s” amount or “tomorrow’s” total as well. It can be modified for days, weeks, months, etc. as long as the time measure is able to be sorted sequentially.
Automatically create and send reports, files and lists on a regular schedule using Microsoft Flow’s recurrence trigger. Whether hourly, daily, weekly or monthly you can deliver the most current and relevant data from SharePoint or OneDrive to interested parties via email without lifting a finger. Combine this with calculated columns in SharePoint and conditions for some awesome possibilities:
Report costs or expenditures above a certain amount
Current month’s birthdays and/or workiversaries to your secretary
Send expenses per department or individual to that department or individual
Budget and salary or payroll figures weekly
Notify when an open ticket is idle for a week or incomplete
Upcoming events per location
Share evaluation status with supervisors for just their employees
Recently closed deals and contracts
Survey responses or reviews under 3 stars
Upcoming deadlines per department
Client info and updates to proper salespeople based on location or product
Distribute new hires’ contact/location info to the organization in weekly batches
Even if your SharePoint site’s regional settings are correct (or whichever data source you’re pulling from), Power BI could convert it to the wrong time zone upon import. It’s a quick fix, luckily. Instead of using your “modified,” “created” or other date field in your report, we’ll create a new calculated column in Power BI to use with an accurate time zone.
Note: This, like many O365 things, is rapidly evolving. If you’re aware of better practices or new updates to licensing, feel free to mention it in comments.
I’m currently at SharePoint Fest Seattle where Chris McNulty, Sr. Product Manager for Office 365 and SharePoint at Microsoft, mentioned (as I understand) there could be changes coming to licensing that would allow more people to consume Power BI reports in a friendlier (more affordable) licensing structure. This would be amazing because currently:
I can create reports. People can’t view data in those reports in a secure way because the entire organization isn’t licensed for Power BI per person above the “free” license.
Specifically I, with a Power BI Pro license, can create reports and place those in SharePoint’s new page experience Power BI web parts (in Preview) but other people (with free or without Pro licenses) cannot view them. They see the following:
Of course, to me as the creator and properly-licensed individual, I see the report perfectly embedded as it should be. And not every organization can afford to license every single user appropriately to be able to simply view embedded reports. Especially if consuming reports (not sharing or building) is the only function they need in the Power BI realm.
In this post, I’ll cover:
How to embed Power BI reports the normal, easy (but license-exclusive) way
Why the webpart (normal, easy way) is cooler than embedding a script
How to embed the report in a (less secure) way so that non-licensed or free-license individuals can actually view and manipulate the data
One of my favorite features of Power BI is the ability to have published reports automatically refresh data on a schedule. This is great for “setting and forgetting” your reports, knowing wherever you publish them they will be showing the most recent data for your clients. I feel like it used to be depending on your license, you could be limited to how frequently you can refresh (max of once per day), but you can refresh nonetheless. And this may have changed, as I couldn’t find (in my brief search) any confirming statement.