PowerBI 1: How to Get Address by Passing Existing Coordinates to Google Maps Reverse Geocoding API

In this article I show you how to get all of these information out of Google Maps by passing existing coordinates to Google Maps geocoding API.

Requirements

To be able to follow this post steps you need to:

  • Have a Google account
  • Activate the Google Maps Geocoding API
  • Find a desired API in Google Developers Guide
  • Use the API in Power BI
  • A GPS device data exported to CSV file

Activate the Google Maps Geocoding API

I assume you already have an existing Google account or you know how to create a new one, so I jump straight into the next step which is activating geocoding API. To be able to start using the Google Maps Geocoding API you need to get an API key first.

Google Maps Geocoding API

  • Scroll down and click “Get A Key”

Get Google Maps API Key

  • This will navigate you to Google Developers Console
  • Click “Yes” then click “Agree and continue”

Get Google Maps API Key 2

  • Type in a name then click “Create”
  • Get Google Maps API Key 3

You successfully created an API key. We will use this key in the next sections of this article.

Using Google Maps Geocoding APIs In Power BI

Now that we have an API key we can use lots of Google Geocoding APIs available here. As you can see there are lots of useful APIs you can use to leverage your Power BI model. In my sample I need to retrieve Country, Post Code and Street Address out of existing coordinates generated by my bike GPS computer. So I use Reverse Geocoding (Address Lookup). Here is a sample:

https://maps.googleapis.com/maps/api/geocode/json?latlng=-36.8484213,174.7457047&key=YOUR_API_KEY

If you copy and paste the above API in your web browser you can see its output which is a JSON providing detailed information about the location.

Note: Do not forget to copy and paste your API key at the end of the API.

The idea is to define a function in Power BI then pass latitude and longitude to the function to get Country, Postal Code and Street Address.

How it works

  • Open Power BI Desktop
  • Get Data from Web
  • Paste the API in the URL section then click OK

Power BI Desktop Get Data From Web

  • Stick with the default and click “Connect”

Power BI Desktop Access Web Content

  • This opens “Query Editor”
  • Click “List”

Power BI Desktop Query Editor Expand List

  • Convert the list to table by clicking “To Table” button from “Transform” tab from the ribbon

Power BI Desktop Convert List to Table

  • Click on expand complex column icon (image) to expand the table then tick “formatted_address” and “types” columns then click OK

Power BI Desktop Expand Complex Column

Power BI Desktop Expand Complex Column 2

  • Expand the “types” column as well

Power BI Desktop Query Editor

  • Filter “types” column to show “Country”, “Postal Code” and “Steer Address”

Power BI Desktop Query Editor Filter Column

Power BI Desktop Query Editor 2

  • We need to the columns as rows so we need to transpose the table. So go to “transform” tab and click “Transpose”

Power BI Desktop Transpose Table

  • As you see the second row contains the column names. Click “Reverse Rows” to show the second row first

Power BI Desktop Reverse Rows

  • Click “Use First Row As Headers”

Power BI Desktop Use First Row As Header

  • Rename the query then save the model

Power BI Desktop Rename Query

Export Power BI Service Data to SQL Server

In this post I explain how to export Power BI Service data to SQL server. YES! You can export data from Power BI service to a SQL Server database sitting in your on-premises environment. Keep reading to see how.

How It Works?

This is going to be a short post as I already covered the first part of the process in my other post on Export Power BI Data to SQL Server with R. So in this post I show you how to use the Power BI Desktop file you already created using the method explained in that blog post to export your Power BI Service data to an on-premises instance of SQL Server. All you need to do is to

  • Publish the existing Power BI Desktop solution to Power BI Service
  • Install On-premises Data Gateway in PERSONAL MODE

Note: R is NOT supported by the current version (Version Number: 14.16.6614.5) of the On-premises Data Gateway in Enterprise Mode.

After you successfully published the model to Power BI Service you’ll notice that you cannot refresh the model if you don’t install the On-premises Data Gateway in Personal Mode.

To see the dataset settings:

  • Login to Power BI Service
  • Click the gear icon (image)on top right of the page
  • Click “Settings”

image

  • Click “Datasets” tab then click on the desired dataset
  • Expand “Gateway connection”

image

You can download On-premises Data Gateway from here. The installation is super easy you just have to keep in mind to

  • Select “Personal Mode” when you’re installing the gateway.

image

  • Now go back to the “Dataset settings” and expand “Gateway connection”. The Gateway should be online on Personal Mode.
  • Tick the “Use your data gateway (personal mode)

image

  • Expand “Data source credentials” and click “Edit Credentials” for all data sources and pass the credentials accordingly
  • To test the solution just navigate to your workspace and refresh the dataset
  • At this point you’ll get a warning message as below. Click OK

image

  • Now query the underlying table(s) in SQL Server database to see if the data has been imported successfully
  • Expand “Schedule refresh” and change the configuration as desired the click “Apply”

image

All done!

 

http://biinsight.com/export-power-bi-service-data-to-sql-server/

PowerBI: How to Get Address by Passing Existing Coordinates to Google Maps Reverse Geocoding API

Create a Function from an Existing Query (Address Lookup)

From the previous tutorial, we loaded the geo-location information for just one coordinate. Now we need to turn the query to a function so that we can invoke it for all coordinates we have in our dataset.

A function construction is as below:

  • Click “Advanced Editor”

Power BI Desktop Advanced Editor

  • Change the code as per the screenshot below ===> “Sample Data single load”
let
  get_geo = ( lat as text, lng as text) =>
let
 Source = Json.Document(Web.Contents
("https://maps.googleapis.com/maps/api/geocode/json?latlng="&lat&","&lng&"
&ampAIzaSyBwKQ7VSYAw4zE0zTtMCQ-k839qoeNdeso")),
 results = Source[results],
 #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"formatted_address", "types"}, {"Column1.formatted_address", "Column1.types"}),
 #"Expanded Column1.types" = Table.ExpandListColumn(#"Expanded Column1", "Column1.types"),
 #"Filtered Rows" = Table.SelectRows(#"Expanded Column1.types", each ([Column1.types] = "postal_code" or [Column1.types] = "street_address")),
 #"Transposed Table" = Table.Transpose(#"Filtered Rows"),
 #"Reversed Rows" = Table.ReverseRows(#"Transposed Table"),
 #"Promoted Headers" = Table.PromoteHeaders(#"Reversed Rows", [PromoteAllScalars=true]),
 #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"street_address", type text}, {"postal_code", type text}})
in
 #"Changed Type"
in
  get_geo

Power BI Desktop Create Function

1– Function internal name

2 & 4 – Input parameters

3 & 5 – Parameters’ data types

6 – Function body

7– Output

Note: The name that will be used to revoke the function is the query name which is “fn_GeoLocation” in our sample. —> “Sample Data single load”

  • Continue modifying the codes as below then click “Done”

Power BI Desktop Function Parameters

  • What we have done above is that we replaced the static latitude and longitude with parameters. We used “&” to concatenate both sides of the source.
  • As soon as you click “Done” Power BI detects the query as a function

Power BI Desktop Invoke Function

  • To test the function just click “Invoke” and enter latitude and longitude then click OK

Power BI Desktop Invoke Function 2

Power BI Desktop Invoke Function 3

  • Delete the invoked function step

We successfully defined a query as a function so that we can invoke it anytime.

The next step is to load GPS data from CSV. I’m using my bike computer data that I exported to CSV format earlier.

Download sample CSV file here.

  • In Query Editor, click “New Source” from “Home” tab then click “CSV”

Power BI CSV

  • Browse and open the CSV file
  • Click OK

Power BI Query Editor CSV

  • Click “Add Custom Column” from “Add Column” tab and name it “Geolocation”
  • Enter the following formula to invoke the fn_GeoLocation function and click OK
  • “Sample Data single load”

Power BI Query Editor Add New Column

Note: I used “Number.ToText” function to convert latitude and longitude data type from number to text as we defined the function parameters as text.

  • You’ll get a “Information is required about data privacy” warning message. Click “Continue”

Power BI Query Editor Add New Column 2

  • Select a desired privacy level from the list then click “Save”

Power BI Privacy Levels

Note: Learn more about “Privacy levels” here.

  • Scroll right to find the new added column
  • Expand “Geolocation” column

Power BI Expand Complex Column 2

Voila! We have got all information needed

  • Click “Close & Apply” from “Home” tab from the ribbon and wait until the data loads into the model.

Power BI Query Editor Close & Apply

Note: It might take sometime to load data into the model based on your dataset size. This is because of sending coordinates to Google Maps row by row then loading the results into the model, so the process could be slow if you have a large dataset. There are also some limitations applied to the API usage.

http://biinsight.com/power-bi-and-google-maps-api-address-lookup/

The integration of SSRS and Power BI

The integration of SSRS and Power BI creates a single portal to access all reporting items; which would be from Power BI dashboard.

For using this functionality, some requirements need to be met. You have to use SQL Server 2016 or higher version. Your dataset of the report should be using saved credentials. SSRS report should have charts, gauges, or maps, because only charts, gauges, and maps can be pinned to the dashboard. Power BI Integration in the reporting services configuration manager should be enabled. And finally; SQL Server agent should be up and running because Agent is responsible for keeping that tile up to date.

Using the integration of SSRS and Power BI; you can have tiles in Power BI dashboard which points to SSRS reports for the detailed paginated report. Power BI users will use normal tiles for interactive reports, and they can use SSRS tiles when they want to see the more detailed paginated report in SSRS.

Will Power BI Eliminate the Need for Data Warehouses?

Recently I’ve had spoke to several peers and discussed with my mentor: why do we still need to build a Data Warehouse if we have Power BI?  For some SMEs (especially in NZ) or departments, may Power BI  well eliminate the need for a data warehouse?

As a fantastic self-service business intelligence tool, one of its key strengths about Power BI is the richness of its query and data modelling capabilities. These allow users to easily combine data from disparate data sources, add complex calculations, and generally model the data so that interactive reports and dashboards can be created in a fraction of the time.

The primary reason that data warehouses are created is to combine data from disparate sources into one accessible source for reporting. If that is the case and companies only need interactive reporting and dashboards, then Power BI will most likely eliminate the need for a data warehouse.

However, data warehouses exist to serve several purposes. Amongst others, here are some of the common reasons that data warehouses are implemented:

  1. Combining data from one or more disparate source systems (aggregating and standardising the data).
  2. Optimise data for reporting workloads (denormalisation).
  3. Reduce load on operational systems.
  4. Tracking historical changes in the data, allowing for point-in-time reporting. And handle the large-scale datasets.
  5. A single point of truth to use for all corporate reporting.

Power BI can, to some extent, cater for the first 3 points above. The 4th and 5th, however, cannot be achieved with Power BI so far. Historical change tracking is something that we will not see in Power BI in a few years. In addition to that, Power BI cannot currently serve as the single point of truth because not all reporting systems can use Power BI as the source for their reporting. Although Power BI is a great reporting tool, it does not cater for every scenario yet. There is still a need for other reporting tools, such as SQL Server Reporting Services, to cater for different business requirements.

Power BI has opened up a world of possibilities for organisations of any size. The small end of town now has the ability to do BI for a fraction of the cost they would have incurred until recently. However, for the large size enterprise requirements, a well-designed data warehouse is still required.

Read more: https://www.linkedin.com/pulse/data-warehouses-do-we-still-need-them-craig-bryden/

Power BI Use Case: Energy Industry

This report focuses on demand forecasting within the energy sector. The views provide region level energy usage status and forecast of future usage for optimizing the operations.

Case Study:

Storing energy is not cost-effective, so utilities and power generators need to forecast future power consumption so that they can efficiently balance the supply with the demand. During peak hours, short supply can result in power outages. Conversely, too much supply can result in waste of resources. Advanced demand forecasting techniques detail hourly demand and peak hours for a particular day, allowing an energy provider to optimize the power generation process. This report focuses on demand forecasting within the energy sector. The report provides region level energy usage status and forecast of future usage for optimizing the operations.


The ‘Energy Solution Forecast’ page shows the demand forecast results from Azure Machine Learning model and different error metrics for user to identify the quality of the model. Temperature and its forecasts are used as a feature in the machine learning model.

energy2


The ‘Energy Solution Status Summary’ page shows the overall status of energy demand of each region. User can select a single region by clicking the filter on the left to investigate each region’s status.

energy

Publish Report to Web from Power BI

With Power BI Publish to web, you can easily embed interactive Power BI visualizations online, such as in blog posts, websites, through emails or social media, on any device.

You can also easily edit, update, refresh or un-share your published visuals.

How to use Publish to Web

Publish to web is available on reports in your personal or group workspaces that you can edit. You cannot use Publish to web with reports that were shared with you, or reports that rely on row level security to secure the data.

You can watch how this feature works in the following short video. Then, follow the steps below to try it yourself.

The following steps describe how to use Publish to web.

  1. On a report in your workspace that you can edit, select File > Publish to web.

  2. Review the content on the dialogue, and select Create embed code as shown in the following dialogue.

  3. Review the warning, shown in the following dialog, and confirm that the data is okay to embed in a public website. If so, select Publish.

  4. A dialog appears that provides a link that can be sent in email, embedded in code (such as an iFrame), or that you can paste directly into your web page or blog.

  5. If you’ve previously created an embed code for the report, the embed code quickly appears. You can only create one embed code for each report.

Tips and Tricks for View modes

When you embed content within a blog post, you typically need to fit it within a specific size of the screen. You can also adjust the height and the width in the iFrame tag as needed, but you may also need to ensure your report fits within the given area of the iFrame, so you also need to set an appropriate View Mode when editing the report.

The following table provides guidance about the View Mode, and how it will appear when embedded.

View Mode How it looks when embedded
Fit to page will respect the page height and width of your report. If you set your page to ‘Dynamic’ ratios like 16:9 or 4:3 your content will scale to fit within the iFrame you provided. When embedded in an iFrame, using Fit to pagecan result in letterboxing, where a gray background is shown in areas of the iFrame after the content as scaled to fit within the iFrame. To minimize letterboxing, set your iFrame height/width appropriately.
Actual size will ensure the report preserves its size as set on the report page. This can result in scrollbars being present in your iFrame. Set the iFrame height and width to avoid the scrollbars.
Fit to width ensures the content fits within the horizontal area for your iFrame. A border will still be shown, but the content will scale to use all the horizontal space available.

Tips and tricks for iFrame height and width

The embed code you receive after you Publish to web will look like the following:

You can edit the width and height manually to ensure it is precisely how you want it to fit onto the page into which you’re embedding it.

To achieve a more perfect fit, you can try adding 56 pixels to the height dimension of the iFrame. This accomodates the current size of the bottom bar. If your report page uses the Dynamic size, the table below provides some sizes you can use to achieve a fit without letterboxing.

Ratio Size Dimension (Width x Height)
16:9 Small 640 x 416 px
16:9 Medium 800 x 506 px
16:9 Large 960 x 596 px
4:3 Small 640 x 536 px
4:3 Medium 800 x 656 px
4:3 Large 960 x 776 px

Managing embed codes

Once you create a Publish to web embed code, you can manage the codes you create from the Settings menu of the Power BI service. Managing embed codes includes the ability to remove the destination visual or report for a code (rendering the embed code unusable), or getting the embed code again.

  1. To manage your Publish to web embed codes, open the Settings gear and select Manage embed codes.

  2. The list of embed codes you’ve created appears, as shown in the following image.

  3. For each Publish to web embed code in the list, you can either retrieve the embed code, or delete the embed code and thus make any links to that report or visual no longer work.

  4. If you select Delete, you’re asked if you’re sure you want to delete the embed code.

Updates to reports, and data refresh

After you create your Publish to web embed code and share it, the report is updated with any changes you make. However, it’s important to know that it can take a while for update to be visible to your users. Updates to a report or visual take approximately one hour to be reflected in Publish to web embed codes.

When you initially use Publish to web to get an embed code, the embed code link is immediately active and can be viewed by anyone who opens the link. After the initial Publish to web action, subsequent updates to reports or visuals to which a Publish to web link points can take approximately one hour to be visible to your users.

To learn more, see the How it works section later in this article. If you need your updates to be immediately available, you can delete the embed code and create a new one.

Data refresh

Data refreshes are automatically reflected in your embedded report or visual. It can take approximately 1 hour for refreshed data to be visible from embed codes. You can disable automatic refresh by selecting do not refresh on the schedule for the dataset used by the report.

Custom visuals

Custom visuals are supported in Publish to web. When you use Publish to web, users with whom you share your published visual do not need to enable custom visuals to view the report.

Limitations

Publish to web is supported for the vast majority of data sources and reports in the Power BI service, however, the following are not currently supported or available with Publish to web:

  1. Reports using row level security.
  2. Reports using any Live Connection data source, including Analysis Services Tabular hosted on-premises, Analysis Services Multidimensional, and Azure Analysis Services.
  3. Reports shared to you directly or through an organizational content pack.
  4. Reports in a group in which you are not an edit member.
  5. “R” Visuals are not currently supported in Publish to web reports.
  6. Exporting Data from visuals in a report which has been published to the web
  7. ArcGIS Maps for Power BI visuals
  8. Reports containing report-level DAX measures
  9. Secure confidential or proprietary information

Tenant setting

Power BI administrators can enable or disable the publish to web feature. They may also restrict access to specific groups. Your ability to create an embed code changes based on this setting.

Feature Enabled for entire organization Disabled for entire organization Specific security groups
Publish to webunder report’s File menu. Enabled for all Not visable for all Only visable for authorized users or groups.
Manage embed codes under Settings Enabled for all Enabled for all Enabled for all

Delete option only for authorized users or groups.
Get codes enabled for all.

Embed codeswithin admin portal Status will reflect one of the following:
* Active
* Not supported
* Blocked
Status will display Disabled Status will reflect one of the following:
* Active
* Not supported
* Blocked

If a user is not authorized based on the tenant setting, status will display as infringed.

Existing published reports All enabled All disabled Reports continue to render for all.

Understanding the embed code status column

When viewing the Manage embed codes page for your Publish to web embed codes, a status column is provided. Embed codes are active by default, but you may encounter any of the states listed below.

Status Description
Active The report is available for Internet users to view and interact with.
Blocked The content of the report violates the Power BI Terms of Service. It has been blocked by Microsoft. Contact support if you believe the content was blocked in error.
Not supported The report’s data set is using row level security, or another unsupported configuration. See the Limitations section for a complete list.
Infringed The embed code is outside of the defined tenant policy. This typically occurs when an embed code was created and then the publish to web tenant setting was changed to exclude the user that owns the embed code. If the tenant setting is disabled, or the user is no longer allowed to create embed codes, existing embed codes will show the status of Infringed.

How to report a concern with Publish to web content

To report a concern related to Publish to web content embedded in a website or blog, use the Flag icon in the bottom bar, shown in the following image. You’ll be asked to send an email to Microsoft explaining the concern. Microsoft will evaluate the content based on the Power BI Terms of Service, and take appropriate action.

To report a concern, select the flag icon in the bottom bar of the Publish to web report you see.

Licensing and Pricing

You need to be a Microsoft Power BI user to use Publish to web. The consumers of your report (the readers, viewers) do not need to be Power BI users.

How it works (technical details)

When you create an embed code using Publish to web, the report is made visible to users on the Internet. It’s publicly available so you can expect viewers to easily share the report through social media in the future. As users view the report, either by opening the direct public URL or viewing it embedded in a web page or blog, Power BI caches the report definition and the results of the queries required to view the report. This approach ensures the report can be viewed by thousands of concurrent users without any impact on performance.

The cache is long-lived, so if you update the report definition (for example, if you change its View mode) or refresh the report data, it can take approximately one hour before changes are reflected in the version of the report viewed by your users. It is therefore recommended that you stage your work ahead of time, and create the Publish to web embed code only when you’re satisfied with the settings.

 

Warning

When you use Publish to web, the report or visual you publish can be viewed by anyone on the Internet. There is no authentication used when viewing these reports. Only use Publish to web with reports and data that the anyone on the Internet (unauthenticated members of the public) should be able to see.


https://docs.microsoft.com/en-us/power-bi/service-publish-to-web

Useful Power BI Visuals Reference

How to choose the right visuals for your reports/dashboards among all these resources?
Here is a  clean reference that lists nearly all the visuals available today, categorized by their case of use. The categories we identified are:

  1. Comparison
  2. Change over time (Trend)
  3. Part-to-whole
  4. Flow
  5. Ranking
  6. Spatial
  7. Distribution
  8. Single

4 Coolest Power BI Tutorial Sites For Beginners

1. Power BI Guided Learning

This is the Power BI official channel. When following this one, you will find many Power BI tutorial videos as well as documentations. You can also use the sample database like AdventureWorks in Power BI desktop then do the trial and error using that database in all visuals. Moreover, you can learn about the tools and other available options in PowerBI desktop through the website. Here’s a protip: work your data on excel before using PowerBI – you’ll save a lot of time.

1


2. Microsoft Virtual Academy

This site provides hundreds of free courses which are designed for Developers, IT Pros, Data Pros, and Students. The topic is diverse, from Cloud Development to Visual Studio to Mobile Development, and more. This site is an excellent way to stay up to date on the latest Microsoft technologies which include Power BI tutorial.  The new course is updated weekly!

2


3. Analyzing and Visualizing Data with Power BI

This course is a part of the Microsoft Professional Program Certificate in Big Data and the Microsoft Professional Program Certificate in Data Science. It will walk you through Power BI, from the basic knowledge i.e how to connect to and import your data, to the advanced level i.e. how to author reports using Power BI Desktop and publish those reports to the Power BI service. The series of short, lecture-based videos are accompanied by demos, quizzes, and hands-on labs to test your knowledge. This free EDX online course is definitely a great resource for learning Power BI.

3


4. Microsoft Technet 

On this website, you can find everything related to Power BI such as key resources, tutorials, case studies and many more. Technet forum is also the best place for you to exchange ideas with Power BI users and gain more knowledge from many Microsoft experts.

4

 

http://niftit.com/get-power-bi-tutorial/