A Fine Slice Of SQL Server
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 closedMarco 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 closedImke 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 closedImke 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 closedGauri 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 closedChris 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 closedJoe 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 closedGilbert 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 closedMarco 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.
Kristyna Hughes puts Power BI to the test:
For example, one instance may have a very large data model that takes a lot of memory and CPU time to refresh, 20 users at peak viewing times, hourly refreshes, and the queries are all very simple and allow for query folding. Another business may have six smaller data models, 950 users at peak viewing times, daily refreshes, and the queries populating the data model are all very very complex. All of these elements impact the usage at any given time, making predicting overall CPU needs nearly impossible. Thankfully, stress testing your capacity gives us an option that is not purely reactionary.
This blog will walk through how to stress test your capacity, the elements of capacity planning, and how to understand the results of the stress test.
Read on to see how, using a step-by-step guide.
Comments closed