Press "Enter" to skip to content

Category: Power BI

Using the Q&A Visual in Power BI

Gauri Mahajan tries out the Q&A visual:

The speed at which the options for data hosting, data processing and data management keep growing, the options for data consumption have also been growing at the same pace. Traditionally, applications and reports used to be the most common and most frequent means of consuming data. As data consumption means matured with time, chatbots, analytics engines, machine learning and artificial intelligence tools and many others. Traditionally, to explore the data, some of the common mechanisms have been using database query languages, preparation of reports by report designers and data exploration in a self-service manner by power users. With the evolution of capabilities like machine learning, artificial intelligence, natural language processing and others, some of the popular and modern methods of data exploration includes natural language-based data analysis, voice-enabled data exploration using smart devices, computer vision-based data analysis, etc. While many of these methods are highly sophisticated and need user training for a user to employ these data exploration methods, natural language-based data exploration is one of the most popular data exploration methods. This method is offered out-of-box by many reporting tools including Tableau and Power BI as well.

The Q&A visual is a really cool concept which works a surprising amount of the time. The problem is that when it doesn’t work, it feels like pushing a string: no matter what you do, it just doesn’t quite do what you need it to.

Comments closed

TIMMEAN() in Power Query

Imke Feldmann has another Excel function to convert:

TRIMMEAN is a statistical function in Excel that calculates the “mean taken by excluding a percentage of data points from the top and bottom tails of a data set”. So you can use it if you want to exclude potential outliers from your data. Daniil Maslyuk has a nice approach for it in DAX, but in here I want to share my M version for Power Query for it.

Read on for the function and how you can use it.

Comments closed

Returning Blank instead of Zero in Power BI

Marco Russo does a bit of transformation:

In matrix visuals, Power BI usually hides rows where all the measures return a blank value. To leverage this behavior or simply to change the visualization of a measure depending on its result, you might want to achieve one of the following:

– Transforming a blank result to zero: this is covered in the article, How to return 0 instead of BLANK in DAX.

– Transforming a zero result to blank: this is the scenario described in this article.

Click through to see what the process looks like.

Comments closed

WEEKNUM() in Power Query

Imke Feldmann translates another Excel function:

If you are looking for the Excel equivalent of the WEEKNUM function for Power Query this article is for you.

As this is also a function that has many regional options, I was lucky to find an algorithm that I could use for its main part here: M functions to convert between ISO 8601 Week & Year ⇄ dates (e.g., 2014-12-29 ⇄ "2015-W01-1") (github.com)

Read on for the code and details on function parameters.

Comments closed

YEARFRAC() in Power Query

Imke Feldmann translates an Excel function to Power Query:

While there is a native YEARFRAC-function in DAX that you can use to calculate year fractions, sometimes you might just need it in Power Query. The function I’m presenting here has the same function arguments than its Excel-equivalent. Its 5 different modes require some advanced calculations. Fortunately I found a great resource on how to calculate it here. So my version here is basically a direct translation into the M-language.

Click through for the code.

Comments closed

Power BI Decomposition Trees

Gauri Mahajan shows off decomposition trees in Power BI:

A large volume and variety of data generally need data profiling to understand the nature of data. One of the aspects of data is hierarchy and inter-relationships within different attributes in data. Hierarchical data is often nested at multiple levels. To analyze the relationship between different attributes in a data that is hierarchical, drill-down and drill-through are two of the most common techniques that are employed for data exploration as well as use-cases like root cause analysis. While these techniques are standard and have been in the industry for quite a long time, figuring out these relationships and navigating hierarchical data can be a challenging task. Data Analysts or Business Analysts typically perform this analysis on the data before presenting it to the end-users. In certain cases, some domain or business users may be required to perform such analysis on the report itself. In that case, the task becomes even more challenging considering the limited data analysis capabilities offered by a reporting tool compared to a database and query languages like SQL. To help power users perform such analysis on a reporting tool, visualizations like decomposition trees can be used to decompose hierarchical data that is presented in an aggregated manner. The Decomposition tree can support both drill-down as well as drill-through use-cases when the user is provided the flexibility to choose the hierarchy or dimensions on-demand. In the Microsoft technology stack, Power BI is the key reporting tool for authoring reports and supports a wide variety of data sources. Power BI offers a category of visuals which are known as AI visuals. One such visual in this category is the Decomposition Tree.

Read on to see how you can create a decomposition tree, what kind of information it shows, and how you can interact with it to learn more about correlations and causes.

Comments closed

DirectQuery and SQL Query Limitations

Chris Webb lays out the limits:

A few days ago I was tagged on a thread on Twitter: my old pal Mim was upset to find that he couldn’t use a SQL query with a Common Table Expression as the source of a table in DirectQuery mode. He worked out why without my help but I thought that it was worth me writing up an explanation for other people who might be struggling with the same problem.

That’s a fairly frustrating limitation, even if you have control of the database you’re querying.

Comments closed

Merging and Appending Queries in Power Query

Joe Billingham shows off two similar-sounding functions:

I had an issue recently where a third-party application had been updated and both the new and legacy versions were being used side-by-side. Logging data from both versions was being written to two separate Azure SQL databases.

The customer needed a Power BI report showing both old and new logging data sets as a single source. If both databases were SQL Server databases, I could have written a view with a cross-database join, imported that into Power BI and thought no more about it. However, the two sources being Azure SQL Databases, with no easy way to join the tables, caused an issue.

This is where the Merge and Append functions in Power Query come in.

Read on for the solution.

Comments closed

Conditional Formatting with the New Power BI Desktop Formatting Pane

Gilbert Quevauvilliers puts a feature’s face on a milk carton:

I am sure everyone can agree that the new formatting pane is an awesome change.

But at the same time, I have found it a challenge to find settings with the new format pane.

In this blog post I will show you to find the conditional formatting which appears to have gone missing in the new format pane?

Click through to find out.

Comments closed

The IN Operator in DAX

Marco Russo and Alberto Ferrari are making a list and checking it twice:

The IN operator in DAX is useful in multiple scenarios to check whether an expression belongs to a list of values. It is oftentimes used along with the anonymous table constructors. IN is syntax sugar for the CONTAINSROW function. Just like CONTAINSROW, IN can be used with multiple columns at once although that syntax is not so common.

Click through to see how you can use IN in your work.

Comments closed