Tag Archives: #powerbi

Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

August 16, 2017 / Erik Svensen

Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

I am working on a project where customer would like to update a PowerBI dataset when specific events occur – in their case when an email with a specific subject arrives in a specific mailbox – and we succeeded by using Microsoft Flow, Azure Functions and PowerBI REST API.

But as I recently bought flic buttons (https://flic.io/) so I wanted to change the setup a bit so the dataset is updated when I click the button – sort of my first IOT project.

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

The ingredients

  • 1 dataset in your PowerBI tenant that can be scheduled for refresh
  • 1 Power BI Pro license (or premium if you want more than 8 refreshes a day)
  • 1 Registration of an Application for Power BI ( link – https://dev.powerbi.com/apps )
  • 1 Azure subscription
  • 1 Power Shell script
  • 1 Azure Function/Logic App
  • 1 Microsoft Flow license in any tenant
  • 1 Flic button
  • 10-15 minutes

First up – the information about the dataset and the group

We need the dataset ID and group ID where the dataset is placed.

The easiest way to find it is to navigate to the settings and the dataset tab and click the data set – the group ID is in blue area and the dataset ID is in the red box,

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

Notice that the Scheduled refresh is set to Off.

Get a client ID for your Power BI Application

You will need to register an application for the update and you can do this via

Sign in and fill out the information.

The App Type has to be Native app and the Redirect URL must be – urn:ietf:wg:oauth:2.0:oob

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

Select both the Dataset API’s and in step 4 click to register the App and save the Client ID.

Powershell script to update the dataset

You can find the Powershell script here – https://github.com/Azure-Samples/powerbi-powershell/blob/master/manageRefresh.ps1

Fill in the $ ClientID, $ groupID and $ datasetID in the script.

In order to avoid popup windows with authentication in the Azure Function I had to modify the script and hardcode the username and password in my script.

$ userName = “username”

$ password = “password”

$ creds = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential” -ArgumentList $ userName, $ password

$ authContext = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext” -ArgumentList $ authority

$ authResult = $ authContext.AcquireToken($ resourceAppIdURI, $ clientId, $ creds)

The bold lines are added and the $ authResult is modified as well.

Azure Function

Log on to the Azure portal – https://portal.azure.com/ and create a Function App

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

Wait for the deployment to finish – it takes about 1-2 minutes.

Choose to create your own custom function

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

Select language Powershell

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

And the HttpTrigger

In my example, I will keep authorization level to anonymous – but you can add more security if needed.

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

Now replace the script with the PowerShell script from the previous section.

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

Now you can save and click run to see if the script works

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

If the script succeeds – you can check whether the dataset is updated via the Power BI dataset refresh history

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

By clicking Get Function URL

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

We will get the POST Url that we will use later in our Flow.

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

Now – Microsoft Flow

Open your Microsoft Flow in your Office 365 portal and create a new blank flow

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

Now we can select between a lot of different triggers that can activate our data refresh in Power BI. It could be certain tweet, a mail in an inbox or in onedrive and many more – we currently have 160 triggers to choose from.

In my example, I want to integrate with a flic button

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

And by connecting to my Flic account I can choose the button I want to use and which Event (it has click, double click and hold) it should be attached to on the button

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

To activate the refresh, I add a new step as an action I choose the HTTP where I can post the URL to the function.

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

After the HTTP action, I add a new step that sends and email to me – with information about the refresh and which button was presses and where it was located when pressed.

Then save the flow and we should be ready to update the dataset via the button

Trying the flow

So by clicking the button we can see the Run History

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

As you can see the flow ran as 23:25

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

And when checking the refresh history in Power BI we can see its refreshed –

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

And I get an e-mail as well

 Updating a #PowerBI dataset using Microsoft #Flow, #Azure Functions and a #Flic button

OBS – The refresh can “only” be done 8 times with a Power BI Pro License but 48 if you should be so lucky that you can have access to a premium edition.

Wrapping up

I have been so fun to create this and it was surprisingly easy – with off course some challenges underway.

But the toolset is amazing and combining Flow and Power BI opens a lot of possibilities and ideas to activate refreshes and do stuff with the Power BI REST API triggered by different events.

I will at least have an extra look at the API and see what can be done – link to documentation

Let me know if you find this interesting.

Advertisements

Filtering Errors in PowerBI

I just faced this problem when trying to analyze some information from google analytics in PowerBI. In my specific situation, I was trying to extract a number from some URL querystrings and filter the rows without the number. When I set the data type of the column to decimal, the rows without a number receive an expression error. Let’s build an example filtering errors in PowerBI.

The image below illustrate a small set of data in excel that will result in this problem. The objective is to extract the numbers and exclude all the rows without numbers. 

PBIError1 Filtering Errors in PowerBI

After importing the excel file in PowerBI, let’s extract all the content after the “=” sign using Transform => Extract=> Text After Delimiter. 

PBIError2 Filtering Errors in PowerBI

PBIError25 Filtering Errors in PowerBI

PBIError35 Filtering Errors in PowerBI

Now, let’s change the data type to decimal using Transform => Data Type

PBIError3 Filtering Errors in PowerBI

We have two errors in this resultset, as you may notice in the images below. There is no way to exclude these errors using the interface, we need to code in M to achieve this. There are two useful functions available to filter errors: Table.RemoveRowsWithErrors and Table.SelectRowsWithErrors, the names already explain what they do.

PBIError4 Filtering Errors in PowerBI

PBIError5 Filtering Errors in PowerBI

We need to open the Advanced Editor, inside View, to edit the M code.

PBIError6 Filtering Errors in PowerBI

PBIError65 Filtering Errors in PowerBI

We will add a new line before the “In” statement. In this line, we will create a new name for our result, like this:

After the “=” sign we can use the function, it has two parameters: The name of the result of the previous line and the column we want to check for errors.

Two more details to go:

  • The previous line need an extra comma at the end
  • After the “in” statement, we need to change the name of the final result set to our new name

PBIError7 Filtering Errors in PowerBI

The final result, without the errors:

PBIError8 Filtering Errors in PowerBI

Let’s block ads! (Why?)

SQL – Simple Talk

Set size for multiple visualizations in #PowerBI at the same time

April 23, 2017 / Erik Svensen

Set size for multiple visualizations in #PowerBI at the same time

 Set size for multiple visualizations in #PowerBI at the same time

When designing your reports in Power BI Desktop you properly spent a lot of time making sure your visualizations is aligned and at least for some of them making sure they have the same size.

So far, we only have the align feature in the Power BI Desktop

 Set size for multiple visualizations in #PowerBI at the same time

To change the size of the visualizations we must use the General properties under Format to resize the elements

 Set size for multiple visualizations in #PowerBI at the same time

But what if you want to resize more than one element at a time – If you select more than one you get the size of the first selection in the general tab

 Set size for multiple visualizations in #PowerBI at the same time

Now here is the trick – modify the width and Height with 1 each

 Set size for multiple visualizations in #PowerBI at the same time

And then back again

 Set size for multiple visualizations in #PowerBI at the same time

And your visualizations have the same size.

OBS – This only works when you select the same type of visualizations – if select different types you won’t be able to see General under Format.

Hope this can help you too –

Advertisements

Tip – Create several AutoSum Measures in with one click #Excel Power Pivot – #powerbi

April 14, 2017 / Erik Svensen

Tip – Create several AutoSum Measures in with one click #Excel Power Pivot – #powerbi

Just wanted to share a quick tip I just stumbled upon when designing a data model in Excel Power Pivot.

For creating a measure in Power Pivot, I place the cursor under the column and then I click the AutoSum button on the Home tab –

 Tip – Create several AutoSum Measures in with one click #Excel Power Pivot – #powerbi

And then I get the formula created quick and I can modify the name.

 Tip – Create several AutoSum Measures in with one click #Excel Power Pivot – #powerbi

But did you know that if you multiselect several cells in the Calculation Area and click the AutoSum – you get formulas for all the columns you have selected

 Tip – Create several AutoSum Measures in with one click #Excel Power Pivot – #powerbi

Hope you find this useful as well …

Advertisements

How to Connect to Dynamics 365 Data from PowerBI Desktop

powerBI report 300x225 How to Connect to Dynamics 365 Data from PowerBI Desktop

PowerBI is a great tool for reporting on and analyzing data, from a wide range of sources, including Dynamics 365. To set this up, you must connect to Dynamics 365 as a PowerBI data source, so PowerBI can load your CRM data. In this blog, we will review the two versions of PowerBI and then provide step-by-step instructions for setting this up in PowerBI Desktop.

Before we begin, if you also want to leverage pre-built Power BI Content Packs with CRM for Dynaimcs 365, check out a previous blog: Connect Your CRM Online Data to PowerBI for Powerful Analysis.

Now, let’s start by talking about the two different versions of PowerBI, PowerBI Service, and PowerBI Desktop.

PowerBI Service, is the online version that you can access from your browser, at www.powerBI.com after logging in. This is the version that is accessed by most users who want to utilize PowerBI for analyzing data, and creating reports and dashboards.

PowerBI Desktop is an application that is installed and run on your PC, like any other Windows application. It is geared for more technical users, who often do the work of setting things up to help regular users who will be using PowerBI Service. The two versions are designed for different purposes, and while they have a lot in common, they are definitely not the same.

Alright! We’ve reviewed the differences, now let’s look at how, in PowerBI Desktop, you connect to and gain access to CRM Online data.

As a preliminary step, we need to know the url to enter in to PowerBI to point it to the Dynamics CRM online org. This is best done by logging in to Dynamics CRM online and copying the url from a screen called “Developer Resources”. These first steps show you how to get to the screen:

1. Log in to Dynamics 365 online.

2. Click on the “Settings” tile.

111816 1532 HowtoConnec1 How to Connect to Dynamics 365 Data from PowerBI Desktop

3. Choose “Customizations.”

111816 1532 HowtoConnec2 How to Connect to Dynamics 365 Data from PowerBI Desktop

4. Choose “Developer Resources.”

111816 1532 HowtoConnec3 How to Connect to Dynamics 365 Data from PowerBI Desktop

5. Copy the url listed under “Instance Web API,” and save it for entering into PowerBI Desktop.

111816 1532 HowtoConnec4 How to Connect to Dynamics 365 Data from PowerBI Desktop

6. With the saved url, we can point PowerBI Desktop to CRM data on Dynamics 365.

7. Start PowerBI Desktop and then select “Get Data” either from the Splash Screen or from the ribbon at the top of the screen.

111816 1532 HowtoConnec5 How to Connect to Dynamics 365 Data from PowerBI Desktop

OR

111816 1532 HowtoConnec6 How to Connect to Dynamics 365 Data from PowerBI Desktop

8. Type CRM into the search box to locate CRM for Dynamics 365 Online. Then click on the “connect” button.

111816 1532 HowtoConnec7 How to Connect to Dynamics 365 Data from PowerBI Desktop

9. Enter the CRM url (saved earlier) and click “OK.”

111816 1532 HowtoConnec8 How to Connect to Dynamics 365 Data from PowerBI Desktop

10. In the “Access an OData feed” dialog box, select “Organizational account” and then click “Sign in.”

111816 1532 HowtoConnec9 How to Connect to Dynamics 365 Data from PowerBI Desktop

11. You may then need to sign in to your CRM for Dynamics 365 Online with your username and password.

111816 1532 HowtoConnec10 How to Connect to Dynamics 365 Data from PowerBI Desktop

12. Verify that you are signed in by making sure you see the message “You are currently signed in” in the dialog box and then click “Connect.”

111816 1532 HowtoConnec11 How to Connect to Dynamics 365 Data from PowerBI Desktop

13. The navigation screen will allow you to select the CRM entities whose data you want to load into PowerBI Desktop.

Note, if you click “Load,all the data and every field in the selected entities will be loaded into PowerBI.

Best practice would be to click “Edit” and select the columns of data you need for reporting.

111816 1532 HowtoConnec12 How to Connect to Dynamics 365 Data from PowerBI Desktop

111816 1532 HowtoConnec13 How to Connect to Dynamics 365 Data from PowerBI Desktop

With your CRM data loaded into PowerBI, you can now start building reports. After you publish them, your PowerBI Service users will be able to build their own dashboards using CRM data.

Happy PowerBI’ing with CRM data!

Let’s block ads! (Why?)

PowerObjects- Bringing Focus to Dynamics CRM

Annual accounts for Microsoft Denmark using #PowerBI and XBRL

May 26, 2016 / Erik Svensen

Annual accounts for Microsoft Denmark using #PowerBI and XBRL

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

The Danish Central Business Register -https://datacvr.virk.dk/data/?language=en-gb& contains a lot of data including the possibility to find the annual account for all companies in Denmark.

These are available in two formats – pdf and XBRL format – so for instance for Microsoft Denmark ApS we can find the latest Annual Accounts (financial statement) via this search

https://datacvr.virk.dk/data/visenhed?enhedstype=virksomhed&id=13612870&soeg=microsoft&type=Alle&language=en-gb

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

As you can see, we can get the document via the link XBRL and get the annual account in XML format like this

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

You can read more about the XBRL format here – https://www.xbrl.org/

So knowing that we can search for companies using their CVR number and find the latest account let’s try to retrieve the data using Power BI Desktop.

Find the company data

Open the Power BI Desktop and choose get data – and the source is Web

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

This will give you the HTML document

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

But we want to find the links to the XBRL accounts so we actually don’t want it opened as an HTML document – so click Edit and in the steps pane click the source to change it to open the document as a Text file

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

And delete the navigation step after Source.

Now we can find all the rows where XBRL is

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

This gives us the three rows with information about the link to the accounts in XBRL format

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

Then trim the column and Split the value by ” – gives us a column 1.8 containing the link to the file.

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

Now we can delete unwanted columns, add an index and filter by 0 in the index column to get the latest account

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

Now we have a table with a link to the latest XBRL account for a given company.

Retrieve the account information

To get specific data in the accounts we can use this XBRL link to do a new query retrieving the data

So click on Home tab and New source and select Web again

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

https://datacvr.virk.dk/data/offentliggorelse?dl_ref=ZG9rdW1lbnRsYWdlcjovLzAzLzQwLzg1LzJkL2ZhL2VjZTItNDViMC1iOTFjLWQzOGUzZTIzZTRhYQ

This gives us the navigator to the XML document

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

If you are interested in specific elements you can tick those – but click for instance Context and click OK and via the Query Settings click source

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

Click on table in the node – http://xbrl.dcca.dk/fsa

And you get all the account lines

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

Then expand the Table column and choose all elements

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

Now in order to get the current years data you can filter by the contextRef c1

Now after some filtering and removing of duplicate rows – we end up with the lines for the profit and loss as seen here

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

The values are all positive so in order to handle this – we can pivot the data and do multiplication with -1 and unpivot the columns again

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

Then we can load this data and visualize the Profit and loss in a waterfall chart

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

By default the waterfall sorts the data by A-Z which isn’t exactly what we want – luckily there is a workaround for this

Choose Edit Queries and add an Index column to the Context query.

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

Load the data and select the Attribute in the field pane – and in the Modeling select Sort by Column and choose to sort by Index

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

And after some formatting etc. we can illustrate the Profit & Loss for Microsoft Denmark like this

 Annual accounts for Microsoft Denmark using #PowerBI and XBRL

Now we can wait a few month to the next Account is done and just click refresh.

Using functions and/or parameters you can make this highly flexible – if this post gets more than 10 likes J – I promise to show you how that can be done.

Hope you liked this – if you want to play around with you can download the pbix here.

Let’s block ads! (Why?)

Erik Svensen

Aligning elements in the Power Bi Desktop Designer – Workaround #powerbi

November 27, 2015 / Erik Svensen

Aligning elements in the Power Bi Desktop Designer – Workaround #powerbi

While we are waiting for the alignment feature of the elements in the Power BI Designer – you can vote for it here, – https://ideas.powerbi.com/forums/265200-power-bi/suggestions/6932400-add-alignment-positioning-and-sizing-controls-for – I have found a workaround that you might find useful.

If you press CTRL while selecting two elements on the page the placeholders for the elements is visible on screen.

 Aligning elements in the Power Bi Desktop Designer – Workaround #powerbi

After selection, you can change the size of the element you want to resize and use the handles of the other element to align with.

OBS – When you have multi-selected elements you can also move them as a group.

This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

Erik Svensen

The European Tour Rankings in Power BI Desktop – #PowerBI

October 4, 2015 / Erik Svensen

The European Tour Rankings in Power BI Desktop – #PowerBI

Today – Thorbjørn Olesen won the Alfred Dunhill Links Championship on the European Tour (Link) – so why not celebrate this by looking at how by building a Power BI Dashboard to analyze the Race to dubai Rankings for 2015.

I know how hard it is to play golf but building the dashboard was quite easy and by using the data from this page – http://www.europeantour.com/europeantour/racetodubai/rankings/index.html – I build a dataset with these few steps.

let

Source = Web.Page(Web.Contents(“http://www.europeantour.com/europeantour/racetodubai/rankings/index.html”)),

Data0 = Source{0}[Data],

#”Changed Type” = Table.TransformColumnTypes(Data0,{{“”, type text}, {“Rank”, Int64.Type}, {“2”, type text}, {“Notes”, type text}, {“3”, type text}, {“Player name”, type text}, {“4”, type text}, {“Nat”, type text}, {“5”, type text}, {“Tournaments Played”, Int64.Type}, {“6”, type text}, {“Race to Dubai Points”, type text}, {“7”, type text}}),

#”Removed Columns” = Table.RemoveColumns(#”Changed Type”,{“”, “2”, “Notes”, “3”, “4”, “5”, “6”, “7”}),

#”Replaced Value” = Table.ReplaceValue(#”Removed Columns”,”,”,””,Replacer.ReplaceText,{“Race to Dubai Points”}),

#”Changed Type1″ = Table.TransformColumnTypes(#”Replaced Value”,{{“Race to Dubai Points”, Int64.Type}})

in

#”Changed Type1″

And then a few measures and modelling in Power BI Desktop – I ended up with this dashboard.

 The European Tour Rankings in Power BI Desktop – #PowerBI

So a total of 307 players from 31 different have won money on the European tour this year and the average money earned is 281K € – not bad for 2 – 4 days of work in the weekends J

Highlighting Denmark in the Treemap shows us that even though Thorbjørn won today he still isn’t the best dane this year in the earnings.

 The European Tour Rankings in Power BI Desktop – #PowerBI

You can download the file from here – Link

Remember you can publish this to your Power BI site and set a refresh every Sunday evening so your dashboard is updated after the weekend tournament.

This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

Erik Svensen

Visualize the danish mobile network history & coverage using #PowerQuery and #PowerMap – #powerbi

July 6, 2015 / Erik Svensen

Visualize the danish mobile network history & coverage using #PowerQuery and #PowerMap – #powerbi

 Visualize the danish mobile network history & coverage using #PowerQuery and #PowerMap – #powerbi

A lot of public datasets is available via this site http://datahub.virk.dk/data/search – currently only in Danish.

One of the datasets contains information about the location of all the antennas in Denmark – http://mastedatabasen.dk/Viskort/ContentPages/DataFraDatabasen.aspx?callingapp=mastedb – and it has an excellent API that enables you to extract data about the antennas.

Here is how you can extract the data using Power Query and then visualize it in Power Map using Excel 2016 – you can do the same in Excel 2013 but I was playing around with Excel 2016.

First I select the

 Visualize the danish mobile network history & coverage using #PowerQuery and #PowerMap – #powerbi

And paste in this query – which will return all the antennas in Denmark – it will return about 27.000 but I set the Max to 100.000

http://mastedatabasen.dk/Master/antenner.json?maxantal=100000

 Visualize the danish mobile network history & coverage using #PowerQuery and #PowerMap – #powerbi

Click the To Table in the ribbon – accept the default and then Expand the column – then you have the data

 Visualize the danish mobile network history & coverage using #PowerQuery and #PowerMap – #powerbi

The rest of the steps is deleting unwanted columns and renaming the fields and changing the established column to a date.

All the steps is as follows

let

Source = Json.Document(Web.Contents(“http://mastedatabasen.dk/Master/antenner.json?maxantal=100000″)),

#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table”, “Column1″, {“vejnavn”, “husnr”, “postnummer”, “kommune”, “idriftsaettelsesdato”, “forventet_idriftsaettelsesdato”, “etrs89koordinat”, “wgs84koordinat”, “tjenesteart”, “teknologi”, “unik_station_navn”, “radius_i_meter”, “frekvensbaand”}, {“vejnavn”, “husnr”, “postnummer”, “kommune”, “idriftsaettelsesdato”, “forventet_idriftsaettelsesdato”, “etrs89koordinat”, “wgs84koordinat”, “tjenesteart”, “teknologi”, “unik_station_navn”, “radius_i_meter”, “frekvensbaand”}),

#”Removed Columns” = Table.RemoveColumns(#”Expanded Column1″,{“etrs89koordinat”}),

#”Expanded wgs84koordinat” = Table.ExpandRecordColumn(#”Removed Columns”, “wgs84koordinat”, {“bredde”, “laengde”}, {“wgs84koordinat.bredde”, “wgs84koordinat.laengde”}),

#”Expanded tjenesteart” = Table.ExpandRecordColumn(#”Expanded wgs84koordinat”, “tjenesteart”, {“navn”}, {“tjenesteart.navn”}),

#”Expanded teknologi” = Table.ExpandRecordColumn(#”Expanded tjenesteart”, “teknologi”, {“id”, “navn”}, {“id”, “navn”}),

#”Renamed Columns” = Table.RenameColumns(#”Expanded teknologi”,{{“wgs84koordinat.bredde”, “Latitude”}, {“wgs84koordinat.laengde”, “Longitude”}}),

#”Removed Columns1″ = Table.RemoveColumns(#”Renamed Columns”,{“forventet_idriftsaettelsesdato”, “kommune”}),

#”Expanded postnummer” = Table.ExpandRecordColumn(#”Removed Columns1″, “postnummer”, {“nr”, “navn”}, {“nr”, “navn.1″}),

#”Renamed Columns1″ = Table.RenameColumns(#”Expanded postnummer”,{{“nr”, “ZipCode”}, {“navn.1″, “City”}, {“tjenesteart.navn”, “Servicename”}, {“navn”, “Technology”}}),

#”Removed Columns2″ = Table.RemoveColumns(#”Renamed Columns1″,{“unik_station_navn”}),

#”Changed Type” = Table.TransformColumnTypes(#”Removed Columns2″,{{“idriftsaettelsesdato”, type date}}),

#”Renamed Columns2″ = Table.RenameColumns(#”Changed Type”,{{“idriftsaettelsesdato”, “Established”}})

in

#”Renamed Columns2″

Then click close and load to – and select to “Only Create Connection” and load the data to the data model in the workbook

 Visualize the danish mobile network history & coverage using #PowerQuery and #PowerMap – #powerbi

Then we can start to visualize the with Power Map

 Visualize the danish mobile network history & coverage using #PowerQuery and #PowerMap – #powerbi

I like the new layer pane in Power Map – it makes is easy to modify the layer – instead of the “old” steps in Power Map in Excel 2013.

In this example I have chosen to plot the antennas by latitude, added the Established as a time and added a filter for LTE – the 4G network.

And now I can click the time line to see the development over time.

I can also see why I need an internet connection in my summerhouse –

 Visualize the danish mobile network history & coverage using #PowerQuery and #PowerMap – #powerbi

Not a lot of antennas nearby – icon sad Visualize the danish mobile network history & coverage using #PowerQuery and #PowerMap – #powerbi

You can download the file here to