You may create a report you wish to share outside of your organization on the Internet. Today, I’ll take a look at the steps to publish your report to the Internet so that the report has all of the same interactivity as the report you created with Power BI Desktop and published within your organization on the Power BI Service.

edit: This article was written on February 29, 2020. All elements contained within are subject to change with the current monthly release cycle the Power BI team is following.

edit: Make sure you only publish information to the Internet that you have cleared through proper channels within your organization.

Publish From Power BI Desktop To Power BI Service.

You cannot publish directly from Power BI Desktop to the Internet (web). You first must publish from Power BI Desktop to Power BI Service. Go back and check this post if you need a refresher how to publish from Power BI Desktop to Power BI Service.

Open The Report In Power BI Service

Log into the Power BI Service, navigate to your workspace, and open the report you wish to publish to the web. Here, I have a screen shot of the Filled Map Report I have been working with in my last few posts. This shows the population change by state from 2010 to 2019 where a darker color indicates a greater percentage change, while a lighter color indicates a smaller or even negative increase.

File Menu…Publish To Web

In Power BI Service, click on the File Menu. From the File Menu drop-down, click on Publish to web

Create Embed Code

You will receive a dialog window to create an embed code…click the button “Create embed code”

Next, you will receive a warning that you are publishing a report on the Internet that anyone can see. If you are sure you want to proceed, click on the button “Publish”

Generate HTML Code

After you click the Publish Button, you should receive the Dialog Window below indicating that everything was successful and you now have the html code you need to embed the report into a blog or website. You also have a link you can send in an email or paste into a browser to see what the report looks like

Here, I just pasted the link in a browser to see what it looks like:

The Embedded Report

Without further ado, here is the report, embedded in this blog, no more screen shots. This report is live. If I make any changes to the report in Power Bi Service, the change will also be captured here. You may use your mouse scroll wheel to zoom in/out on the map. You can hover on a state with your mouse to see the tooltip for each state. If you do not have this functionality, it is because you have enabled many security extensions on your browser that are blocking the correct functionality of the map. Try another browser such as Microsoft Edge

Admin/Tenant Settings

If you cannot publish your report to the web, it is because your admin for your organization has disabled Publish to web. Ask your admin to enable this feature.

Tidy Up

That’s it for today. Stay tuned for whatever is next….wit? rancor? rant? sarcasm? bramble?…most likely the latter

, , , , ,

Today, I’ll take a look at the process to publish a report from Power BI Desktop to Power BI Service so you can share your reports within your organization.

Power BI Desktop Version

I’m using version 2.78.5…. released ~ 2/8/2019. If you are using a different version, your results may vary.

Power BI Desktop Report To Publish

Open the report you want to publish to your workspace on the Power BI Service. Here I have a Filled Map based on population change by state from 2010 to 2019. Go back to this post if you need to review how to create the Filled Map.

Click To Publish

Click on the publish icon in the Share Group on the Home Ribbon in Power BI Desktop.

Sign Into the Power BI Service

If you are not already signed into the Power BI Service, you will be prompted to sign in. Enter your email address

and your password

Select Workspace

Once you successfully log into the Power BI Service, you will be prompted to choose a workspace to publish the report to

Replacing Dataset

If you already have a report with the same name already published to the workspace, you will receive a warning that you are about to replace the original with the new – be careful if that is not what you intend to do.

Publishing…Success

You should receive a message that the report was successfully published to the Power BI Service.

Power BI Service

Next, I’ll check that the report is indeed published to my workspace in the Power BI Service. I need to log into my Power BI account – powerbi.microsoft.com

and password:

Navigate To The Workspace

Navigate to the workspace you published your Desktop Report to. I published to “My workspace”, so I’ll select that.

After you navigate to your workspace, click on the Reports Menu at top

In the list of reports, find the report you published from Power BI Desktop

Check the report to make sure it looks just the report you created in Power BI Desktop. I tried Firefox and Chrome Browsers and the Conditional Formatting did not render correctly. Most likely because I have several extensions enabled for both of those browsers. I then tried to open the report in the Microsoft Edge Browser where the report rendered as expected with the correct conditional formatting.

Tidy Up

That’s it for today. In the next post in this series, I’ll see if I can publish to a public web page so that the report is live and interactive.

, , , , , ,

Well said by Jim Rohn. Today, I’ll look at at how to format a filled map (Choropleth) in Power BI.

Power BI Desktop Version

I’m using version 2.76.5…. released ~ 12/18/2019. There are no newer updates as of 2/15/2020. If you are using a different version, your results may vary.

Report Window

From the Data Window click on the Report icon on the left. This will stitch the view from the Data Window to the Report Window and expose the Visualization Pane

Create The Filled Map

Click on the Filled Map Visualization Icon in the Visualization Pane. The Filled Map Visualization will appear on the Report Canvas.

Click on the Filled Map on the Report Canvas. This will expose the Location Fields at the bottom of the Visualization Pane. Once the fields are exposed, drag the State Field from the Data Pane to Location. Lastly drag the Pcnt Change Field from the Data Pane to Tooltips in the Data Fieelds at the bottom of the Visualization Pane.

You should now have a filled map that looks something like the image below.

Conditional Formatting

Now I need to add divergent colors to the filled map so I can quickly see which states have the greatest percentage change in population from 2010 to 2019.

  1. Click on the Filled Map on the Report Canvas
  2. Click on the Format Button Below the Icons in the Visualization Pane
  3. Click on the drop-down for Data colors
  4. When you mouse over Default Color notice that a vertical elipses is visible
  5. Click on the vertical elipses
  6. Click on the fx Conditional Formatting pop-up

The Default Color – Data color dialog opens. The State is the default field for Based On Field – click on the drop-down and change to Pcnt Change.

Change the Colors for Minimum and Maximum to 2 different contrasting colors. I’ll use a very light grey and black.

Interpret the Visualization

Even at this small zoom level, it is easy to see some differences in population increases and decreases. It appears as though West Virginia, Illinois and Mississippi had negative to very low population increase. On the other hand; Nevada, Texas and Utah had large population increases.

The only one I found surprising was Utah at 16% increase from 2010 to 2019. I had heard of all of the others through social media and or news outlets over the past few years.

Divergent Color

You can add a 3rd color to the Conditional Formatting if you so choose. Tick the checkbox for Diverging and you will receive a middle value input for the middle color as well.

Custom Color – HEX Only, No RGB

I come from Excel where I am used to using RGB (Red, Green, Blue) values for creating custom colors. RGB is not an option in the Default Color Dialog – you have to use hexadecimal values.

Hexadecimal Resources

I found a good resource for working with hexadecimal colors at color-hex. Make sure you check it out.

Tidy up

That’s it for today. Grab the workbook from my OneDrive. Post inspired by this post at PowerBI tips by Mark Carlo and Seth Bauer.

, , , , , , , ,

Another classic quip from Mark Twain (Samuel Clemens). Perhaps not one of his better known, but apropos for displaying numbers.

Power BI Desktop Version

I’m using version 2.76.5…. released ~ 12/18/2019. There are no newer updates as of 2/15/2020. If you are using a different version, your results may vary.

Load Some Data

I grabbed some population data from Wikipedia to look at US Population percent change by state from 2010 to 2019. I had to clean the data a bit because it included Totals, Region Totals, and Territories – I just want the 50 US States.

I’ll see what happens if I try to change the Data type of the Pcnt Change column in the Data Model

  1. Click on the Data icon in the x pane
  2. Click on a cell in the Pcnt Change column
  3. On the Modeling Ribbon, click on the drop-down for the Data type.
  4. Change the Data Type from Text to Decimal Number

You will receive a Data type change warning. Go ahead and click on the Yes button to confirm the change

Note the difference in the appearance of the data in the Pcnt Change Calculated Column. Before the change, the data is left-aligned, after the change, the data is right-aligned.

Format as Percentage (%)

Now that I have a decimal value in the Calculated Column, I’ll change it to a percentage. On the Modeling Ribbon, in the Formatting Group

  1. Click on the % icon
  2. In the decimal places drop-down, I changed from 2 decimal places to 1 decimal place

Formatted Tooltip

Now the tooltip is nicely formatted and easy to ready without 9 decimal places.

Tidy up

That’s it for today. Grab the workbook from my OneDrive. Post inspired by this post at PowerBI tips by Mark Carlo and Seth Bauer.

, , , ,

There is an update for Power BI Desktop available this morning, Tuesday, February 18, 2020. Download is here. No official announcement on the Power BI Blog as of this morning.

, ,

In Part I of this series, I demonstrated how to use Range Objects, Arrays and VBA to Filter and Delete data from a table. Today, I’ll use Power Query to Filter data from Table A based on criteria in Table B.

Load Data

I’ll begin by loading data from an Excel Workbook to Power Query in Excel. Here is the data I created in Part I of the series where I grabbed NFL winning percentages for all 32 NFL Teams. I replicated the data 9 more times giving me a total of 320 records.

Just like in Part I, I want to filter out the 4 teams in the NFC East. So, if everything works correctly, I should have 280 records when I am done.

Load Data Into Power Query

In Excel, click on the data menu (1). In the Get & Transform Data Group A(2), click on the Get Data icon (3).

In the pop-up menu, click on From File (4). From the next pop-up menu, select your file type. I’m using an Excel Workbook as my data source, so I’ll select Excel Workbook.

From the Import Dialog Window, navigate to the file that has your data, highlight the file (6) and click on the import button (7).

In the Navigator Dialog Window, tick the box next to “Select Multiple Items” (8). Next, tick the box next to the name of each worksheet you wish to import into Power Query (9). In this instance, there are 2 worksheets and I want to import both worksheets. Lastly, click on the Transform Data Button (10).

The data is now loaded into Power Query. There are two tables showing in the queries pane. The table with all records is named “Data” (Table A) and the table with the 4 teams I wish to filter out of the data are in the table named “crit” (Table B).

Now that I have all data that I need loaded to Power Query, I want to perform any transformations on the data and/or tables that are needed before I can use Table B as a filter on Table A.

Transformations

Power Query added a few transformations when I imported the worksheets into Power Query. The other transformation I need, is to convert the criteria table (Table B) into a List.

  1. Click on the crit Table in the Queries Pane
  2. Click on the Transform menu across the top of the Power Query Window
  3. Click on the Convert To List icon in the Any Column Group

The crit table (4) has been converted to a list (5). Note the different icon in the queries panel.

Filter Manually

Let’s take a look at what happens when I deselect the 4 teams that I want to remove from the filter drop-down in the Data Table. in the Text Filters drop-down (1), I removed the tick marks for the 4 NFC East teams (2)

A new Applied Step is added to the Applied Steps Pane (3). There are now 280 records (4) which is what I was after.

All well and good, however I want to use the list as the list of items to exclude, I don’t want to manually deselect teams. It is easy enough for this example, but imagine if I have a much larger table with many more items I need to exclude. Let’s take a look at the expression created when I manually deselected the 4 teams:

= Table.SelectRows(#"Changed Type", each ([Team] <> "Dallas Cowboys" and [Team] <> "New York Giants" and [Team] <> "Philadelphia Eagles" and [Team] <> "Washington Redskins"))

The expression is telling Power Query to return all Rows except those where the team is Dallas Cowboys, New York Giants, Philadelphia Eagles, or Washington Redskins. Those are the 4 teams I have listed on my criteria list “crit” so I need to replace

"Dallas Cowboys" and [Team] <> "New York Giants" and [Team] <> "Philadelphia Eagles" and [Team] <> "Washington Redskins"

with

List Contains

Also note in the expression, that Power Query is using the Applied Step Name #”Changed Type” in the place of the Data Table, so I will want to use the same Applied Step in my revised expression:

= Table.SelectRows(#"Changed Type", each List.Contains(crit,[Team])=false)

That is much easier to read. Note that I used =false to exclude the teams from the crit list. Here is the data table with the revised expression showing the 280 records as expected.

List Changes – Data Updates

The idea is to pass a dynamic list to filter the data table so I’ll check to see what happens to the data table when the list changes. I added the Cincinnati Bengals to the data source, saved and closed the workbook.

I returned to the Power Query Editor and refreshed all to update the list and the table. I added a fifth team so the list should return the 5 teams and the record count in the data table should now be 270.

I now have a List (Table B) that is filtering a Table (Table A). The expression now refers to a a list so that the filter is dynamic – as the data source expands and/or contracts, I can refresh all and the Data table will show more or fewer records depending on the teams listed in the data source.

Power BI Desktop

The steps are the same if you are using Power BI Desktop.

Tidy Up

That’s it for today. Grab both files from my OneDrive

Stay tuned for Part 3 in the series.

, , , , , , , ,