Worth a Read

Paginated reports in Power BI offer a rich set of capabilities for printing, and the generation of report documents. Paginated reports can be exported to multiple document formats, and those exports can be scheduled and delivered via email. Unfortunately, for the moment at least, paginated reports require the use of... Read more

Power BI is without question the best way to report on data in SharePoint lists. The query tools available in Power Query make working with SharePoint data relatively painless, an the cached dataset means that reports are run against an optimized copy of the list data, not the data itself.... Read more

Power BI V2 workspaces recently (May 2019) entered into general availability. The biggest difference between a V1 and V2 Power BI workspace is the fact that a V2 workspace is not backed by an Office 365 group, and a V1 workspace is. One area that this change affects a great... Read more

Power BI datasets and dataflows are the two native data sources for Power BI reports. Connecting to a datasets allows a report to be built against an existing Power BI dataset in place, and dataflows represent a source of data that has had transformations applied to it. When connecting to... Read more

If you are new to Power BI, or if you’ve worked with Power BI Desktop, you’re familiar with the concept of refreshing data. By default, Power BI caches data which needs to be refreshed on a periodic basis. Reports that use Direct Query datasets do not need to have their... Read more

Microsoft data connect is a new technology for extracting data in bulkj from the Microsoft Graph. This article outlines how this data can be transformed with Databricks, and loaded into Power BI dataflows.... Read more

At the end of 2018, SharePoint received something that we haven't seen for a long time – a new column type, Location. Location columns will look up an address and geocode it as it is being entered in a form. It will also separate all the constituent parts of the address... Read more

Unlock all of the power of Power Query in Power BI dataflows... Read more

Reporting on SharePoint data has been a requirement for a long time, and there have been many approaches to fulfill this need. Custom web parts, Data View web parts and SSRS direct connected reports have historically been some of the solutions, but they all suffer from the same problem. If... Read more

The recent availability of the SharePoint 2019 public preview, and the supporting information that accompanies it has clarified the status of Business Intelligence features in SharePoint 2019. This release, with one exception, is the culmination of the process of decoupling BI from SharePoint which began in SharePoint 2016 through the... Read more

As discussed in a previous post, Working with Excel Files in the Power BI Service, Excel and Power BI have a rich, complex relationship. Power BI Desktop is the primary design tool for Power BI, and it has many feature overlaps with Excel as an analytic tool. Excel can be... Read more

Power BI has been able to work with Excel files since it was first introduced. Indeed, it was born from the analytic capabilities in Excel. Users can connect directly to Excel files by using the Power BI service and nothing but a browser. However, depending on the content of the... Read more

This post is the sixth and final post in a series exploring Power BI and complex data types in SharePoint. This post examines the various options in Power BI for working with lookup fields. The previous posts are: Using Power BI to Report on Multi-Value SharePoint Fields Using Power BI... Read more

This post is the fifth in a series exploring Power BI and complex data types in SharePoint. This one details the use of SharePoint Hyperlink or Picture fields. The previous posts are: Using Power BI to Report on Multi-Value SharePoint Fields Using Power BI to Report on Person Fields in... Read more

This post is the fourth in a series exploring Power BI and complex data types in SharePoint. It discusses working with using Power BI to report on rich text fields from SharePoint. The previous posts are: Using Power BI to Report on Multi-Value SharePoint Fields Using Power BI to Report... Read more

This post is the third in a series exploring Power BI and complex data types in SharePoint. The first post explores working with multi-value columns, and the second post covers working with person fields. In this one, we’ll explore some of the nuances of working with managed metadata fields Managed... Read more

Power BI has garnered a lot of attention in the past few years as the “place to be” for Microsoft business intelligence. Power BI is of course Microsoft’s cloud platform for dashboarding and reporting. On premises, Microsoft’s reporting platform has traditionally been SQL Server Reporting Services (SSRS) and is now... Read more

This post is the second in a series exploring Power BI and complex data types in SharePoint. The first post explores working with multi-value columns. In this one, we’ll explore some of the nuances of working with person fields Person fields in SharePoint are just a special case of the... Read more

Power BI has direct support for reporting on SharePoint lists and documents whether SharePoint is on-premises or in the cloud. Getting at your data is relatively straightforward if you know the URL, but as I’ve written about before, SharePoint data can be idiosyncratic. Text and number fields work right away,... Read more

Three years ago I wrote an article about how to map your Facebook check in in Excel using Power Query and Power View. Given that Power View has gone the way of the Dodo for all intents and purposes, I have been meaning to rewrite it using Power BI to... Read more

If you’re a Power BI Desktop user, you are likely familiar with the monthly update cadence. Once per month, a new Power BI Desktop is released, and normally it contains at least one new feature that you WILL want to use. In case you miss it, a little indicator will... Read more

If you are in information worker of any sort, and have been at it for any more than a couple of years, you’ve experienced it – the X: drive. Or the S: drive, P, R, whatever the letter. It’s the drive letter that is mapped to a network based file... Read more

Power BI licensing has changed again. This week at Microsoft Ignite, Microsoft introduced a new capacity based SKU for Power BI Embedded, intended for ISVs and developers: The A SKU. This brings the number of capacity based SKUs to 3, with each category having numerous sub categories. This means that... Read more

When SQL Server 2016 was released, I wrote a post that covered how to integrate SharePoint 2016 with SQL Server Reporting Services (SSRS) in native mode. I wrote that post because although SSRS was still available in SharePoint Integrated Mode, many of the new features were only in Native mode.... Read more

Power BI Report Server (PBIRS) was first introduced in May 2017. Based on SQL Server Reporting Services (SSRS), it brings the ability to work with Power BI reports completely on premises in addition to all the other capabilities of SSRS. Given this, it would be reasonable to conclude that PBIRS... Read more

A short time ago, I posted an article explaining the difference between a Power BI Pro license, and Power BI Premium capacity, and the fact that you’ll at least need one or the other in order to share a Power BI report on a SharePoint page via the Power BI... Read more

Microsoft today made available the August 2017 preview of Power BI ReportServer 2017. This preview includes the long awaited support of embedded data models, as well as the ability to render Excel reports natively. This is a major step forward, because with this release, Microsoft has completed its vision for... Read more

NOTICE Sept 17 2017 – The central thrust of this post is incorrect. I am leaving it here, because it still contains valid information, but for an update, please go to this article –  Which Premium SKU is Needed to embed Power BI Reports in SharePoint and Microsoft Teams I... Read more

The Power BI web part is now a part of SharePoint online for the majority of Office 365 users. This web part allows Power BI reports to be embedded on SharePoint pages, putting them in greater context. These web parts are rendered on the client, not on the server like... Read more

On April 12, 2016 Microsoft will no longer offer extended support for SQL Server 2005.  This means no more security updates or hotfixes.  However, the bigger reason to upgrade is the fantastic performance improvements offered in SQL 2014.  SQL Server 2014 has been shown to be 13X faster than 2005,... Read more

This blog is original content written for the Microsoft MVP Award Program Blog, posted here. SQL Azure is a compelling platform for data storage. It’s cheap, reliable, and highly accessible. However, it’s not identical to SQL Server on-premise, and there are a number of things to be aware of when... Read more

Until now I’ve been using a service to set up separate environments to connect to my clients.  The multitude of VPN software can mess with my machine so I prefer to keep them separate.  Lately I’ve had a lot of challenges with the service, and some down time along with... Read more

I ran into this when trying to enable Hyper-V on my machine.  I found that the option for Hyper-V Platform was greyed out so I couldn’t select it.  When I hovered over the message was "Hyper-V Cannot be installed: Virtualization is disabled in the firmware".  In order to make it... Read more

There have been a number of times over the years when I have had to create a Proxy user in SQL Server in order to provide needed access to connections and locations being used in an SSIS package.  Sometimes the SQL Server Agent login account simply doesn’t and shouldn’t have... Read more

PowerPivot for Excel is a fantastic addition to Microsoft’s Business Intelligence offering.  It provides a self-service capability for users to mash-up huge amounts of data from multiple sources, create advanced calculations that get refreshed alongside their pivot tables and the ability to very easily publish workbooks as web applications.  Excel... Read more

CodePlex has a great new toolbox item for SSIS projects in Visual Studio 2012.  It’s called the SSIS Excel Refresh Task. It allows you to refresh a whole Excel file or single queries of your Excel sheet.  It works for PowerPivot models as well. There isn’t a lot of instruction... Read more

Last month I had the great honour of winning a Microsoft MVP Award.  Most of my friends and family don’t really understand what a big deal it is to win this award.  I was over the moon.  This is an award that Microsoft gives to folks who have contributed their... Read more

Here is a sweet little script that can check which stored procedures are running and perform an action.  In this case I need to update a configuration table if the stored procedure is not running. BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with... Read more

I like to use my blog as a library for myself, to be able to easily find solutions I have used before.  Here is a simple example of querying an XML string using SQL.  In this example I have inserted one XML string into a temporary table, with two items... Read more