Connect your #PowerBI desktop model to #Tableau Desktop via External Tools in PowerBI

I recently created an external tool to PowerBI desktop that connects your Power BI desktop model to Excel (https://eriksvensen.wordpress.com/2020/07/27/powerbi-external-tool-to-connect-excel-to-the-current-pbix-file/) and then I thought – could we also have a need for an external tool that could open the desktop model in Tableau desktop.

So, I downloaded a trial version of the Tableau Desktop to see what is possible.

And sure, enough Tableau can connect to Microsoft Analysis Services and therefor also the localhost port that Power BI Desktop uses.

We can also save a data source as a local data source file in Tableau

Which gives us a file with a tds extension (Tableau Data Source)

When opening the file in Notepad we can see the connection string and some extra data about metadata-records.

It turns out that the tds file does not need all the meta data record information – so I cleaned the tds file to contain

Opening this file from the explorer will open a new Tableau Desktop file with the connection to the specified model/database/server.

The external tool

Knowing this I could create an external tool the same way as my Excel connector.

First create a PowerShell

OBS – in order to run a powershell script on your pc you need to have to set the execution policy – https://go.microsoft.com/fwlink/?linkid=135170

The PowerShell script

Function ET-TableauDesktopODCConnection
{  

	[CmdletBinding()]
    param
    (
        [Parameter(Mandatory = $false)]        
		[string]
        $port,
        [Parameter(Mandatory = $false)]        
		[string]
        $database,
        [Parameter(Mandatory = $false)]        
		[string]
        $path	
    )
    
        $tdsXml = "<?xml version='1.0' encoding='utf-8' ?>
<datasource formatted-name='LocalPowerBIDesktopFile' inline='true' source-platform='win' version='18.1' xmlns:user='http://www.tableausoftware.com/xml/user'>
  <document-format-change-manifest>
    <_.fcp.SchemaViewerObjectModel.true...SchemaViewerObjectModel />
  </document-format-change-manifest>
  <connection authentication='sspi' class='msolap' convert-to-extract-prompted='no' dbname='$database' filename='' server='$port' tablename='Model'>
</connection>
</datasource>"   
                
        #the location of the odc file to be opened
        $tdsFile = "$path\tableauconnector.tds"

        $tdsXml | Out-File $tdsFile -Force	

        Invoke-Item $tdsFile

}

ET-TableauDesktopODCConnection -port $args[0] -database $args[1] -path "C:\temp"

The script simply creates a tableauconnectort.tds file and stores it in C:\temp – and the xml content in the file is dynamically referenced as arg(0) and arg(1) when the external tool is called from Power BI Desktop.

Save the script in C:\temp and call it ConnectToTableau.ps1.

The OpenInTableau.pbitool.json file

Next step was to create a pbitool.json file and store it in C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools

{
  "version": "1.0",
  "name": "Open In Tableau",
  "description": "Open connection to desktop model in Tableau ",
  "path": "C:/Windows/System32/WindowsPowerShell/v1.0/powershell.exe",
  "arguments": "C:/temp/ConnectToTableau.ps1 \"%server%\" \"%database%\"",
  "iconData": "data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAJAAAACQCAYAAADnRuK4AAAABmJLR0QA/wD/AP+gvaeTAAADRklEQVR4nO3dv27TUBiH4WPEitSRS+iCurO0GzdRiS5sXRhAXZhYEAxd2LoUiZtgaxb2iqWXwFiJCzgsqPRPrMb5Jc1x/TxbqgSi5O2xE3+uSwGAUeo2/QRac3R8cla6bvfqB7XOPr19s7e5Z9S2J5t+AoybgIgIiIiAiAiIiICICIiIgIgIiIiAiEziUMbR8cnZovetXbfTlbJ1dbuUy67W80UfP7XDHk83/QQexPVjW/fd9e7trSGPnxqbMCICItLEJqyeljrv593BivbRap0tfNdwH2hVDj58mfuanH5819R+axMBrduQHdvb80BdredT2zEewiaMiICICIiIgIgIiIiAiAiIiICICIiIgIhM4lDGEA5bDGMFIiIgIgIiIiAiAiISTbf1TRK2ZmWTjQvomyRszaomG61ARAREREBEBEREQESaOMdo7eeFjdBYzguzAhEREBHjHP/8fv/i3i8An3/+1dTmowVWICICIiIgIgIiIiAiAiIiICICIiIgIgIiIiAiSx8Lc3Xjcdk/nJ2VWv+/X103+/51dy/9d61ARAREpIlxjilPHvZpbfKwjxWIiICICIiIgIgIiEgTn8KGWmQAfiz/79gH9a1ARG7UP5arG29qBVqHZAXaP5ydDbj7Tqn16v0qXXdZSln4/eo77HFzE+bqxuNy/djW8MdulVLi98smjIiAiNzchI3w6saT1nULv18l3AfqfQrLPnCT80B2ooczD0STRvlF4jp+a/11juVYgYgIiIiAiAiIiICINPEp7Of29txPQC8vLib7qefZq29zX5M/P1439ZpYgYgIiMjSmzCnMY/LKg5bzGMFIiIgIgIiIiAiAiIiICICIiIgIgIiIiAiAiLSxDhHCwzML8cKRERARKJlu2+SsDUPOdnYN0nYmlVNNlqBiAiIiICICIiIgIg08eWZ88Lucl4YkyAgIgIiIiAiAiJinOOWdf0108fKCkREQEQERERARAREREBEBEREQEQERERARCZxKGPw1Y1v3R7y+Kkd9mgioLVPHjZwdeOhWps87GMTRkRARJrYhK1dA1c3fqxGsZ19SOaBhrEJIyIgIgIiIiAiAiIiICICIiIgIgIiIiAAAAAYjb8VJdQbiRXyOAAAAABJRU5ErkJggg=="
}

Test it

Now restart your Power BI desktop and the external tool should be visible in the ribbon

Then open a pbix file with a model and hit the button.

A PowerShell screen will shortly be visible and then Tableau opens the tds file and now we have a new tableau book with a connection to active power bi desktop datamodel.

And we can start to do visualizations that are not yet supported in Power BI –

How can you try it

You can download the files needed from my github repository – link

Feedback

Let me know what you think and if possible share some of the viz that you make.

Using Power Query and Power Map to visualize public data of the windmills in denmark

While preparing a training session for a customer within the Windmill industry I found public data that lists all the windmills in Denmark with their size and location.

The data can be found here – http://www.ens.dk/info/tal-kort/statistik-noegletal/oversigt-energisektoren/stamdataregister-vindmoller

So I decided to see what Power BI could do with this dataset.

The data

The file with the latest data (http://www.ens.dk/sites/ens.dk/files/byggeri/anlaegprodtilnettet.xls is named the same way and is structured in a way that makes it easy to make custom analysis and visualisations.

So first tool to use in the Power BI stack…

Power Query to the rescue

First – switch to the Power Query tab and choose to load data from File and Excel File and paste the address to the file in the file open – it does take some time but it will work.

And Power Query will open and list the tables that exists in the file

The data we are interested in starts at row 18 and contains a lot of columns that we aren’t interested in.

And there is also some formatting of columns from text to numbers etc.

Here is the total list of steps shown

Create a Power Query function to create Latitude and Longitude

The data file has doesn’t contain Latitude or longitude but the coordinates is listed with the “European Terrestrial Reference System” – so I had to find a way to convert the values into Latitude and longitude.

The Danish Geodata Agency provides a free webservice that can do the conversion for us – http://geo.oiorest.dk/documentation/api/koordinat.aspx – examples.

So for instance the first wind mill is located at

http://geo.oiorest.dk/wgs84.html?etrs89=6171175,720898.4

And inspired by Rafael Salas – blog post – http://www.rafael-salas.com/2013/08/power-query-creating-function-to.html – I decided to create a Power Query function to do the calculation.

So I created a blank Query and added the following query

The function takes two arguments the east and north coordinates, and uses those coordinates to get and XML table from the Web service. As I run with a comma as decimal separator on due to my Danish regional settings I have to replace the comma with a dot as the web service requires the decimal separator to be a dot.

The result is loaded into a table with the “bredde” – latitude and “Længde” – longitude

And the function returns both of these columns.

Then I can use that function in my first query by adding a Custom Column.

And expand that to get both latitude and longitude as columns in my query.

And voila the custom calculated columns.

Then some number formatting of some columns and renaming to English heading and we are ready to send the data to Excel

The data back in Excel

Returning the query to Excel will then give me the list of all 5.126 running windmills in Denmark per September 2013.

Pretty awesome – it does take about 2-3 minutes for it to update/lookup all the geo location – but only one isn’t matched and that is due to an empty row.

Using Power Map to visualize the data

With the data nicely washed and geotagged we can use Power Map to visualize the data.

Power Map’s first guess on the geomapping is actually very good. The “Kommune” is set to county – which is correct and because I named the columns Latitude and Longitude these are automatically also linked correct.

With the Geography properly matched we can move “Next” to visualize the data.

So for instance the KWH by Supplier

Or the KWH by Region/County

Or the world biggest sea wind mill park

Or a heatmap

This is so much fun and finally let make a video of the development over time.

Power map video

Power map also enables us to create a timeline and play the timeline. The data has the date of when the windmill was established so we can use that date to visualize how the windmills have evolved over time.

So by adding the established field to the Timeline – power map can visualize the development over time.

This can be created as a video.

Creating the 45 sec video at the medium format took about 10 minutes to create – so be patient.

Here is a link to the video – http://sdrv.ms/17cBIrx

The file

You can download the example file – here

Comments please

You are more than welcome to add a comment whether you find this cool or not J