Press "Enter" to skip to content

Category: Power BI

Data Lake Analysis With Excel And Power BI

Published 2017-07-25 by Kevin Feasel

Sachin C Sheth announces support for Azure Data Lake Store within Excel and Power BI:

Until now, if you had to analyze data stored in ADLS with Excel, you would have to copy it into a relational data store like Azure SQL Data Warehouse or download the data onto a machine, and then use Excel to analyze that data. This was rather cumbersome involving additional cost and time. With this new support, you can now access files stored in ADLS with Excel in-place, without having to copy them to other stores or locations. You can quickly get advanced insights into raw or prepared data. Models and queries you have created using Excel that ran against local data, can be run seamlessly against data stored in ADLS.

Security capabilities of ADLS allow administrators to control access to the data stored in ADLS in a discretionary manner. With this you can limit the access that Excel users have for the data in ADLS. In this manner, data in the ADLS-based data lake continues to be the single source of truth with no redundant copies and can be analyzed by analytics tools of your own choice .

Click through for a demo video.

Comments closed

Maps In Power BI

Published 2017-07-21 by Kevin Feasel

Reid Havens shows off the different map visuals within Power BI:

ArcGIS Map

The most recent addition to the Power BI Map family. It’s supported by a company called Esri, and is a very feature rich map visual! What makes this visual stand out is that you can overlay whatever data you have with public geographical data such as demographics, weather, and even historical data. It’s highly customizable and offers multiple ways to visualize data with maps, and that’s even before you start adding the public data sets! Can you tell that I like this visual a lot? Because I do! visualize data with maps

Now I could easily spend an entire blog post JUST outlining all the ways to use this visual, but I’ll stick to the highlight reel. It can visualize data with maps using the bubble or fill method similar to the other map visuals, albiet with a few more customizations and tweaks. However, one of the unique features of this visual is the heat map option! Any of you familiar with Power Maps in Excel has probably seen this before…well now we have it in Power BI. I find this data visualization super useful in identifying data clustering based on location.

Read on for additional varieties of maps you can create.  I personally think the bubble map is ugly and that one map with pie charts (thankfully not shown in Reid’s post) is hideous, but there are some very good map visuals available to us.

Comments closed

SUM() Or SUMX()

Published 2017-07-19 by Kevin Feasel

Matt Allington explains when to use SUM() or SUMX() in DAX:

Example: Total Sales SUMX = SUMX(Sales,Sales[Qty] * Sales[Price Per Unit])

SUMX() will iterate through a table specified in the first parameter, one row at a time, and complete a calculation specified in the second parameter, eg Quantity x Price Per Unit as shown in the example above for the current filter context.  Once it has done this for every row in the specified table in current filter context, it then adds up the total of all of the row by row calculations to get the total.   It is this total that is returned as the result.

This is a really good explanation of the topic.

Comments closed

Wanted: Filtering Power BI Data Views

Published 2017-07-13 by Kevin Feasel

Rob Collie is rabble-rousing and building up a pitchfork mob:

I don’t know how we’ve managed to go this long without the ability to Filter in the Data View:

As simple as it sounds, this is the #1 thing I miss when I leave the Excel Power Pivot environment for Power BI Desktop.

Why do I miss it?  Debugging and Validating, primarily.  When I’m getting results from a measure that I don’t trust, it is super common for me to filter a table in Power Pivot so that I’m just looking at a specific set of rows.  And then I often filter by a second column, and then sometimes a third, in order to see if there are any unexpected surprises in the data.  Scrolling through thousands of rows looking with my eyes just doesn’t cut it.

This is positively crucial, at least for me, and creating a Table or Matrix visualization to perform the same task is such a high-friction alternative that I’m usually tempted to start over in Power Pivot.  Ugh.

In fact this is the overwhelming #1 reason why, if I’m starting a model from scratch, I try to do as much of the work as I can in Power Pivot before converting over to PBIX format.

Rob has provided a link to the Power BI Ideas forum, making it easy to vote this item up.

Comments closed

Plotly And Power BI

Published 2017-07-10 by Kevin Feasel

Leila Etaati shows how to use Plotly to generate interactive R charts in Power BI:

In the last two posts (Part 1 and 2), I have explained the main process of creating the R custom Visual Packages in Power BI. there are some parts that still need improvement which I will do in next posts. In this post, I am going to show different R charts that can be used in power BI and when we should used them for what type of data, these are Facet jitter chart, Pie chart, Polar Scatter Chart, Multiple Box Plot, and Column Width Chart. I follow the same process I did in Post 1 and Post 2. I just change the R scripts  and will explain how to use these graphs

Leila includes several examples of chart types and shows that it’s pretty easy to get this working.

Comments closed

DAX’s ALL() Function

Published 2017-07-06 by Kevin Feasel

Matt Allington explains what the ALL() function is in DAX and when you might want to use it:

The ALL() function seems very simple on the surface however it has layers of complexity.  In its most simple usage it is a function that simply returns a table (virtual or materialised).  The syntax for ALL() is as follows

=ALL(TableOrColumn,[Column2],[ColumnN]..)

ALL() will always return a table, not a value.  Because it is a table, you cannot put the result directly into a cell in a Pivot Table or a Matrix.  Think about it, you can’t put a table with (potentially) multiple columns and (potentially) multiple rows into a single cell in a visual – it wont “fit”.

There’s a lot to ALL() and Matt does a great job explaining it.

Comments closed

Neural Nets With R And Power BI

Published 2017-07-05 by Kevin Feasel

Leila Etaati continues her series on using neural nets in Power BI:

we are going to predict the concrete strength using neural network. neural network can be used for predict a value or class, or it can be used for predicting multiple items. In this example, we are going to predict a value, that is concrete strength.

I have loaded the data in power bi first, and in “Query Editor” I am going to write some R codes. First we need to do some data transformations. As you can see in the below picture number 2,3 and 4,data is not in a same scale, we need to do some data normalization before applying any machine learning. I am going to write a code for that (Already explained the normalization in post KNN). So to write some R codes, I just click on the R transformation component (number 5).

There’s a lot going on in this demo; check it out.

Comments closed

Using Power Query’s Combine Feature

Published 2017-06-28 by Kevin Feasel

Matt Allington explains how to use the Combine feature in Power Query:

Now the good news is that somehow the combine button has done a reasonable job at combining the files (see 1 below).  Actually I wanted to unpivot the month columns, but I will come back to that later.

The bad news is all of the activity over on the left hand side.  What the…?  There are 5 additional “Queries” on the left (numbered 2 – 6) that do all sorts of things.  Let me tell you what each of these are and then come back and explain how to use/interpret these things.

2. This is a parameter that can be used to change the sample file

3. This is the link to the sample file that was selected originally (I selected the first file in the folder and this is the link to that file).

4. This is the “by example query” – the most important query to know about.

5. This is an auto generated function that goes with 4 above.

6. This is the final output query (it is the query that is displayed in 1 above).

Matt clearly explains the whole process, so read on if you need to combine files of Power Query.

Comments closed