Power BI + Office 365 Forms

Today got challenged by a colleague on how to explore Microsoft Forms data with Power BI.

The easy answer is: export the Excel file from the “Responses” menu and explore it with Power BI:

But the goal was to avoid this manual process! There is a simple solution, just need to follow the following steps:

1 – Download the Excel using the “Open in Excel”

2 – Go to your download history and grab the url:

Copy the url into the following M code:

    Source = Web.Contents("PASTE URL IN HERE"),
    Excel = Excel.Workbook(Source),
    Table1_Table = Excel{[Item="Table1",Kind="Table"]}[Data]

Authenticate with your Office 365 credentials:

And your done! The data is ready to explore in Power BI and off course refreshable:

Note: The form url has a parameter “maxResponseId” that needs to be increased if you want to download the future entries, I’ve tried to input a maximum value of ~1000 and had no issues

PowerBIPS + Service Principal

Last February Microsoft announced that its possible to authenticate with service principal.

This allows you to perform operations on the REST API without an actual user account (master account) or a Power BI PRO license assignment.

Learn here on how to create the Service Principal and configure it to use in your Power BI Tenant. In resume the steps are:

  1. Register an App in Azure AD
  2. Create a Security Group and add the App as a Member
  3. Enable Service Principals to use the Power BI APIs and add the Security Group above
  4. Add the App to the workspace as Admin

Regarding  PowerBIPS to use a Service Principal you should get the token using the following code:

$tenantId = "MyTenant.onmicrosoft.com"
$clientId = "Application Id"
$clientSecret = "Application Secret"

$authToken = Get-PBIAuthToken -clientId $clientId -clientSecret $clientSecret -tenantId $tenantId

With the token you can call any of the cmdlets like this:

$reports = Get-PBIReport -authToken $authToken -groupId "Workspace id"

Note: Service Principals are only supported over new Power BI Workspace model and doesn’t work with admin APIs

Power BI App + Feedback Forms

A lot of times people ask me why Power BI is the best BI platform in the world (which by the way it is :)) and I always tell them that if you evaluate by the feature:

  • Visualization
  • Data transformation
  • Data modeling
  • AI

Probably Power BI is not the best on any single feature but combined, in my opinion, excels every other tool (Simpson Paradox?)

And another very important aspect to consider when evaluating a BI Platform is the ecosystem and on that aspect Power BI + Office 365 provide a unique set of possibilities that can really make you a lot more productive:

In this post I’ll give you another example of these kind of integrations on the Office365 Power Platform:

Create a Microsoft Form to collect user feedback about Power BI App and add the link to feedback in the app

First you need to create the form:

Get the form Shared link:

And using the awesome new App Navigation experience just add the feedback link in the App Navigation like this:

Now if your user click on “Give us Feedback” the form will open inside the app:

As a form admin you can see all the feedback results:

Is this awesome or not?! And you can create this in 10 minutes!

Next steps:

Analyse in Excel + Power BI Desktop

Connecting Excel do your Power BI Desktop database has many useful applications, for example you can use Excel to do some quick and advanced testing on the values of your Power BI model.

Power BI Desktop has an Analysis Services Instance behind the scenes that you can indeed connect and build Pivot Tables with Excel, the problem is discovering the TCP port of that instance.

A simple solution would be to install DAXStudio, connect to PBIDesktop and then inspect the port on the bottom of the window:

Which is fine if you do it a couple of times, but if you want to be constantly connecting to Power BI Desktop a better alternative would be to:

  1. Install the PowerShell module “PowerBIPS.Tools” that is available on the PowerShell Gallery
  2. Register a context menu entry that will save on the folder an ODC connection file to every Power BI Desktop file opened





QuickTip – Split a delimited text into a Table in DAX

Today I was challenged with a problem and did not find an easy solution to it online, so I decided to write this quick post on it.

The challenge was to create a measure in Table1 that would sum on Table2 all the Rows whose first character was a match with any of the categories on a text separated by “|”:


The problem is that the “Categories” column is a text separated by “|” and there’s no built in DAX function to convert a delimited text into a table.




Hope it helps.

Create a Power BI Push DataSet using Power BI Desktop w/ PowerBIPS.Tools

I have already covered how to create an API Enabled (Push DataSet) Power BI Dataset here and here.

The datasets are created using our open-source powershell module PowerBIPS and you need to write some code like the following:

$dataSetSchema = @{name = $datasetName
; tables = @(
    @{name = "Counters"
	; columns = @( 
		    @{ name = "ComputerName"; dataType = "String"; isHidden = "true"  }
		    , @{ name = "TimeStamp"; dataType = "DateTime"  }	
			, @{ name = "CounterSet"; dataType = "String"  }
		    , @{ name = "CounterName"; dataType = "String"  }
		    , @{ name = "CounterValue"; dataType = "Double"  }
	@{name = "Computers"
	; columns = @( 
		    @{ name = "ComputerName"; dataType = "String"  }
		    , @{ name = "Domain"; dataType = "string"  }	
			, @{ name = "Manufacturer"; dataType = "string"  }		        		        			
    ;measures = @(
        @{name="Average CPU"; expression="CALCULATE(AVERAGE('Counters'[CounterValue]) / 100, FILTER('Counters', 'Counters'[CounterSet] = ""Processor(_Total)"" && 'Counters'[CounterName] = ""% Processor Time""))"; formatString="0.00%"}
; relationships = @(
        name = [guid]::NewGuid().ToString()
        ; fromTable = "Computers"
        ; fromColumn = "ComputerName"
        ; toTable = "Counters"
        ; toColumn = "ComputerName"
        ; crossFilteringBehavior = "oneDirection"      


$dataSetSchema = New-PBIDataSet -authToken $authToken -dataSet $dataSetSchema -Verbose	

It’s very straightfoward, but if you need to create complex measures and relationships it’s easy to commit some mistakes if you dont have the help of Power BI Desktop.

To make this task easier we added a new cmdlet to our PowerBIPS.Tools module: Get-PBIDataSetFromPBIDesktop

You just need to create a Power BI model as usual, use “Enter Data” option to create your tables (w/ sample data) and then create the relationships, measures, custom columns, write your DAX w/ intellisense:




And in the end use the Power BI Desktop model to create a Power BI Push DataSet like this:

# Get a PBIDataSet schema from PBIDesktop
Get-PBIDataSetFromPBIDesktop -pbiDesktopWindowName "*WindowName*" -datasetName "PushDataSet"

# Create the REST API dataset on powerbi.com
New-PBIDataSet -dataSet $dataSet -groupId "workspace id"

Let me know your opinion!