How do you access data in a Text/CSV file using PowerBI?

It is quite easy as long as you have a good Text/CSV file.

I have previously used this file FFD.txt in earlier posts.

https://hodentekmsss.blogspot.com/2016/12/creating-csv-file-from-sql-server-2012.html

I am going to use the same file in this post.

Launch PowerBI and Click GetData.

Click Text/CSV submenu item. This brings up an 'Open' window as shown which lets you search through your folder system.

 Locate the FFD.txt file on your drive and click Open. The file is displayed in the PowerUI as shown.

Click Load at the bottom of the above.

 The data gets loaded as shown.

Now click on Data in the left as shown and the data is displayed.

This is all folks. It is quite easy to bring in Text/CSV data into PowerBI

What are the new features in the June 2018 update of Power BI?

Get the latest June 5, 2018 version of Power BI Desktop from here:

https://powerbi.microsoft.com/en-us/desktop/?WT.mc_id=Blog_Desktop_Update

Get all the Power BI related posts on this site:

HodentekMSSS -Pure SQL Server

Get the version suited to your architecture.

List of new features and stuff (copied from Power BI site):

Here’s the complete list of June updates:

Reporting

• High contrast support for reports
• Donut radius control
• Pie and donut detail labels position control
• Format data labels separately for each measure in a combo chart
• Longer phone reports

Custom visuals

• Organization Chart
• China Heat Map

Modeling

• Filtering and sorting in data view
• Improved locale formatting

Data connectivity

• SAP BusinessWarehouse connector improvements
  • New driver and improved performance
  • Improved support for hierarchy variables
• Spark connector now supports Windows Authentication
• OData V4 connector enhancements
• ODBC connector improvements
  • Folding support for Top Rows
  • Ability to filter navigation by DSN catalog

Other

• National cloud selector

How to add a SLICER to my report in Power BI?

SLICER control provides filtering capability to the report, both Basic and Advanced.

Here is a clustered column chart from one of my previous posts.
The data of Male/Female/Total populations for several states in India is shown.

This uses a Clustered Column chart shown.

The next image shows the data behind the above report.

Only very few of the Indian States haven been chosen for the report and if you had had all of the States in this view it will be more crowded. Now how do I just filter a State from this report. You can do this by adding a SLICER (shown in the next image and you find thiss under VISUALIZATIONS).

You click and add the SLICER. The SLICER gets added to the report as shown. Here is a List of all STATES in the report. Instead of a List , you could also opt for a Drop-down list.

Now using the Basic filtering you can manipulate the report to show the one for the STATE you choose in the LIST as shown (Herein PUNJAB is chosen).

In addition to Basic you also have Advanced filtering where you can impose further conditions on your choice for the states.

Using the condition that the STATE name starts with 'C', the number of States is reduced. Now you can choose from this reduced LIST.

Can you sort and filter data in Power BI?

Actually this is one of the new features in the June 5, 2018 update of Power BI. In the other posts 1 , 2 ()you can review enhancements to Donut and Pie charts.

Here in, the improvements in this update allows you to sort columns and filter them ver effectively.
I am using the data of from Indian Census from one of my earlier posts here .
https://en.wikipedia.org/wiki/Demographics_of_India

This was imported into MS Excel and it was in turn imported into Power BI.
The data in Excel is shown here:

Sort_Filter_0
The data from this post is as shown in Power BI:

Sort_Filter_1

In the June 5, 2018 Power BI used here, you can sort and filter in each column.

Column 2 which is text (name of Indian State), you have the sort option come up when you
click the column2's handler as shown.

Sort_Filter_2

For Columns 3 to 5, you have other filtering options as well as sorting:

For example if you choose to filter in the state column(Column2). Here only states starting with 'A' are chosen.

When you agree for this filter by clicking OK, you would see this,

Does Power BI support Microsoft OLE DB provider for ODBC for data access?

Power BI supports only the .NET Framework OLE DB Provider for ODBC and does not support the Micrsoft OLE DB Provider for ODBC (MSDASQL).

If you create a Data Link file such as this one (ExcelNov.udl)  I created from an EXCEL file:

Provider=MSDASQL.1;Persist Security Info=False; Data Source=Excel64Test;Initial Catalog=C:\USERS\OWNER\DESKTOP\BLOG2017\MSSS2017\suiggy.xlsx

You cannot connect to EXCEL file using the Connection string such as the above. First of all, it does not support the attribute, Persist Security Info=false. Even if you remove this attribute, you will not be able to connect.

Review the following screen shots attempting to connect to EXCEL file using its Data Link, ExcelNov.udl.

Accessing the OLDDB option in GetData.

Using the connection string:

Removed the Security Information and trying to connect.

The ExcelNov.udl file is OK and the connection is successful but not for Power BI.

What is treemap and how do you use it in Power BI?

A treemap represent an entity of items in a rectangle where each item occupies a rectangle and all entities are inside the single rectangle( not unlike a map of a state containing all the counties, a map unlike treemap is not in a rectangle).

This is supported visualization type in Power BI.

Treemap.png

I am using the data from this post  of filtered sales by employees in the Northwind database on SQL Server 2016 using a direct query.

On a new page, click treemap under visualization to add the treemap to the page as shown,

Treemap_0

Drag fields LastName and UnitPrice to the treemap and the treemap changes as shown. Each rectangle now corresponds to an employee with its area in proportion to the sales made by the employee.

Treemap_00

You can add other formatting as shown

Treemap_000
You can add Spotlight to the treemap using the Spotlight menu as shown.

Treemap_2

You can also add tooltip in the Group page.

Treemap_3

How do you configure Donut labels inside or outside the donut?

Again this feature is available in the June 5, 2018 update of Power BI.

Visual appeal is really important and Power BI now supports labelling inside or outside the Donut and Pie charts.

We use the same data used in the previous example of controlling the inner radius of Donut control used in data visualization described in this post.

Here the labels are outside the Donut.

Here is how you make them move inside the Donut.

You can also have label's text to overflow if it is not contained.

Some labels inside and others outside.

Overflow set to Off.

In the same way PIE Charts are also handled.

How to control the radius of a Donut chart in Power BI?

Visual appeal is everything for anything. It is also called eye candy, loosely put. It is aesthetics that matters.

For Donut charts this makes a big change. Now you can control the inner radius.

This is a new feature of June 5, 2018 update for Power BI. You need to install this version first before you follow this post.

Get some fields' data into Power Bi by connecting to your datasource as in this post.

Drag and drop a Donut Control to your page as shown.

Donut_00

Place check marks for the data in Query1 as shown.

Donut_0

Click Format shown here:

Donut_1

The default is Inner Radius=60%

The next two figures shows for 28% and 88% inner radii.

Can you connect to a SQLite database using Power BI ?

Yes, you can.

Power BI is the Microsoft product for business intelligence. SQLite is well known for its use in mobile platforms and embedded apps.

There is no native way of connecting to SQLite using Power BI, but you could use the ODBC driver which was described in an earlier post.

Read here:
ODBC Drivers for SQLite:

Read here:
Creating ODBC DSN for SQLite3:
https://hodentekmsss.blogspot.com/2018/04/creating-odbc-dsn-for-connecting-to.html

Connecting to SQLite3 using Power BI is easy. Here are the steps.

Launch Power BI and open the Get Data menu as shown.

In the list in the Other clic on ODBC.

Highlight the DSN, ODBCSQLite and Click OK. 

Change it to Windows (this may not be necessary) and click Connect.

The From ODBC window is displayed. Click on Advanced Options to display the pane where you can write a SQL Statement.

It goes through from processing and since there is no table by that name, it returns an error.

Now change the SQL Statement to the following:

Select * from Users

After some processing it get connected.

Now you can find the table details in Navigator as shown.

Power BI does not show the tables and write a query you need to know the objects you created in SQLite database.