Bringing US Climate data to Excel is super easy!

You can ge the US climate data from this site.


It has data for all the states, like in this image.

USClimateData

This shows the data for Honolulu.

USClimateData_Honolulu

All you need to do is to highlight the data you want to use in Excel as shown,

Copy it.

Paste it into the first cell of an empty sheet.

Once it is in EXCEL you can use the built-in functionality of charting data.

You now have Geography and Stock data types in MS Excel office 365

Stocks and Geography are the new data types in MS EXCEL Office 365.

I think you must have specific versions of Office 365 for you to work with these new data types.

Microsoft is excited but I am not, as I may have to pay up for (or upgrade subscription) something I may only use to see what it can do.

The nice thing about these are they are AI Powered and they search things out of Microsoft Knowledge Graph, the service that powerss their search engine, Bing.

What it does is it has a context and the search is in this context.

These are shots of Excel sheet with the Geography data type from the above link.

Power BI and Microsoft OLEDB Provider for ODBC Drivers

Power BI does not support the Microsoft OLEDB Provider for ODBC Drivers also known as MSDASQL.

Power BI uses the .NET Framework Data Provider of OLEDB (System.Data.OLEDB).
This was found while connecting Power BI to Microsoft Excel using the connection string developed using a (Universal Data Link) UDL file.
----------
[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Excel7_11;DBQ=C:\Users\Owner\Desktop\Blog2017\MSSS2017\Hindu_Islam_old.xls;DefaultDir=C:\Users\Owner\Desktop\Blog2017\MSSS2017;DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;";Initial Catalog=C:\Users\Owner\Desktop\Blog2017\MSSS2017\Hindu_Islam.xlsx
----------------------------
Also Power BI does not support the Persist Security Info attribute set to false.
This is the error message about OLE DB Provider support.

Using a Map in MS EXCEL is very easy

Excel provides excellent, easy-to-use MAP element that can be inserted into an Excel spreadsheet. It is easily accessed from the Insert menu item as shown. Of course your data must have geographical data.

Let us get some data into a work sheet. Click create Data and use the option to get from the Web. Let us use some data from Wikipedia which in turn shows data from Indian census. Let us use the Basic option.

Click OK.

You will get the next window that you are accessing the web content anonymously.

Click Connect. The connector starts connecting to the Demographics_of_India source.

When the connection succeeds you can access a bunch of data that comes into the Navigator pane of  a pop-up window as shown.

There is a wealth of data to be analyzed in the Navigator. We just choose the one shown, ‘Population between age 0-6 by state/union territory. This displays the data in the Table View pane as shown above.

Click Load to load the data into your Excel application in Sheet 1 as shown.

Excel has this nice feature of displaying the Recommended charts as you begin to insert a chart. It also shows how to do it as shown next. When you choose the whole data and try to bring in a Map, you will sent to BING (map provider) because BING  has the right map to provide and of course you need to accept their terms. When you accept, Map of the World comes up which now focuses on India as shown in the next window.

Note that it has already added the Min and Max values of the data. Clicking any particular map region shows the data for that region.

Now you can pretty up the map with all sorts of details using the Chart Elements.

Don't you think that was easy as 1-2-3?

Data Bars are new in Power BI (June 2017)

Looking at a bunch of data does not give a feeling for the big and small values without making comparisons by reasoning. This next table is trivial in that you can easily see the small and big values. However I have chosen this to make the explanation easy to understand.

This comparison is built into the idea of presenting data bars in Microsoft programs such as MS Excel and Microsoft SQL Server Reporting Services 2012.

In the June 2017 version of Power BI Desktop which is in preview 'Data Bars' are supported. What Data Bars do is to show visually how big or small data values are. Here is an example from my previous post how the data is visualized after adding Data Bars.

Four types of Data Bars are shown in the above: Data Bars, Color Scales, Icon sets and 'Above average' types.

In the present version this is how 'Data Bars' feature is implemented.

I have imported the same data as in the previous post into Power BI using 'Get Data'.

I added the two fields 'State' and 'Item Value' into the fields as shown above.
 I had to rename 'State' to 'St' as 'State' may be keyword in Power BI which automatically formats as s map (this was on another page of the view)

This is probably some 'bug' in the software in this Preview version here.

Now right click the 'Item Value' in the fields as shown.

In the Conditional formatting menu choose 'Color scales'.

Here you can choose colors to represent Minimum, Middle and Maximum values and click OK.

That is all there is to it.