Blog
We write articles and blogs on Microsoft Business Intelligence technologies on a regular basis. The list below showcases all our content sorted chronologically - you can filter through the content by post topic.
Total Articles: 36
Dynamic Coalesce in PowerQuery
We can define coalesce in PowerQuery as the last value existing in a single row of multiple columns positioned from left to right. In our example that would be the last value from the month columns. In this article, we will show you how to create both static and dynamic formulas for coalescing. If you
Dynamic bulk insert to Power Apps collection
In one of our latest blog posts, we described a technique used to copy tables from other programs (e.g. Excel) into PowerApps collections. This solution had a drawback. Within the code, the column order was referenced to determine which column to collect. This means that, for the first column in the PowerApps collection, it gets
Bulk insert to Power Apps collection
In this blog, we will show you the technique used to collect tables for your app by using the bulk insert. We will show you how to make the same table structure in Power Apps as Excel tables. We will use Excel to show you how to copy-paste tabular data from it and collect it
Why should you start using Power Apps?
In this article, we will take a look at what is Power Apps, why should we use it, and when organizations can benefit from custom apps created in the Power Apps platform.Many organizations are not yet familiarized with Power Apps capabilities, and the ways it can improve their agility, modernize and automate manual processes. What is Power Apps? The official Microsoft
Merging with date range using Power Query
In this blog, we will see how to merge two tables in Power Query based on key column and a date range. Merging with multiple matching columns is straightforward and can be achieved in Power Query by selecting merge queries option and holding CTRL key when choosing matching columns like in the picture below:
How to process multiple folders with a single PowerQuery script
Let us imagine the following scenario. We have multiple clients sharing their sales data with us. They store the data in dedicated folders that reside in different places. The number of clients, as well as the number of files for each client, will vary across periods. We wish to create an automatic refresh system that
Total Articles: 21
Dynamic Coalesce in PowerQuery
We can define coalesce in PowerQuery as the last value existing in a single row of multiple columns positioned from left to right. In our example that would be the last value from the month columns. In this article, we will show you how to create both static and dynamic formulas for coalescing. If you
Merging with date range using Power Query
In this blog, we will see how to merge two tables in Power Query based on key column and a date range. Merging with multiple matching columns is straightforward and can be achieved in Power Query by selecting merge queries option and holding CTRL key when choosing matching columns like in the picture below:
Dynamically expand table or record columns in Power Query
In this article, we will show how to solve a common problem of expanding nested tables or records in Power Query, when the column names of the nested objects vary in numbers. In the second part of the article, we will show a more robust technique for dealing with changing structures of the nested tables.
Sorting in Matrix created with calculation groups (PowerBI)
This is the second part of the blog series dedicated to showing workarounds for currently unsupported matrix visual features. In the first part, we explained how to add conditional formatting only for the specified columns. In this article, we will explain how to introduce sorting. Below, you can test a working example in the embedded
Conditional formatting with Calculation Groups in PowerBI Matrix visual
This is going to be the first of 2 articles explaining how to introduce conditional formatting and sorting when using calculation groups in Matrix visual. It would be great if conditional formatting and sorting could be applied to a calculation item, but until this happens, here is a workaround. Link to the second part of
Including hours and minutes in DAX date filters
When filtering data by date, we can simply use a date slicer which is enough in most cases. We rarely need to filter our data by hours and minutes. However, sometimes this approach is necessary for the sake of the analysis. In this blog, we will examine the scenario where we need to include hours
Total Articles: 12
Sorting in Matrix created with calculation groups (PowerBI)
This is the second part of the blog series dedicated to showing workarounds for currently unsupported matrix visual features. In the first part, we explained how to add conditional formatting only for the specified columns. In this article, we will explain how to introduce sorting. Below, you can test a working example in the embedded
Conditional formatting with Calculation Groups in PowerBI Matrix visual
This is going to be the first of 2 articles explaining how to introduce conditional formatting and sorting when using calculation groups in Matrix visual. It would be great if conditional formatting and sorting could be applied to a calculation item, but until this happens, here is a workaround. Link to the second part of
Including hours and minutes in DAX date filters
When filtering data by date, we can simply use a date slicer which is enough in most cases. We rarely need to filter our data by hours and minutes. However, sometimes this approach is necessary for the sake of the analysis. In this blog, we will examine the scenario where we need to include hours
Pivottables inside the PowerBI – The DAX way
PivotTables, or pivoting, is by far the best analytical power of Excel. You digest the data and play with it on the fly. Heck, PowerBI is pretty much PivotTables with pretty visuals, just a lot smarter (because of DAX). While PowerBI offers many stunning looking visuals including matrices, they do not include out of the
How to split budgets to lower granularity with DAX (PowerBI)
When creating a budgeting prediction, most of the time we are creating it on the higher granularities. Rarely we do it on a daily basis. But sometimes we are required to match budgets to actual sales on lower granularities than the one we previously set (e.q. the manager wishes to see how would the budget
Optimizing distinctcount with reduced memory consumption in PowerBI (VertiPaq)
High cardinality columns are often the problematic part of the data modeling in most environments, including PowerBI. Since the PowerBI data model resides on a columnar database, having high cardinality columns impact both performance and the size of the model. This is especially the case with columns having more than 2 millions of distinct values.
Total Articles: 14
Dynamic Coalesce in PowerQuery
We can define coalesce in PowerQuery as the last value existing in a single row of multiple columns positioned from left to right. In our example that would be the last value from the month columns. In this article, we will show you how to create both static and dynamic formulas for coalescing. If you
Merging with date range using Power Query
In this blog, we will see how to merge two tables in Power Query based on key column and a date range. Merging with multiple matching columns is straightforward and can be achieved in Power Query by selecting merge queries option and holding CTRL key when choosing matching columns like in the picture below:
How to process multiple folders with a single PowerQuery script
Let us imagine the following scenario. We have multiple clients sharing their sales data with us. They store the data in dedicated folders that reside in different places. The number of clients, as well as the number of files for each client, will vary across periods. We wish to create an automatic refresh system that
Dynamically expand table or record columns in Power Query
In this article, we will show how to solve a common problem of expanding nested tables or records in Power Query, when the column names of the nested objects vary in numbers. In the second part of the article, we will show a more robust technique for dealing with changing structures of the nested tables.
How to track changes in the table in Power Query
If you ever had a need to check if anything changed in the new table compared to the previous export, no matter in which column, then this is the article for you. We have created an M script in Excel that uses the left anti join to check for data inconsistency. it works with any
Split packs into single products in Power Query
This is going to be an easy-to-follow article on how to use PowerQuery to easily change source data granularity for only a subset of data. We used a client’s request to split packs into its components as an inspiration to write this post. You can also download Excel workbook and follow along. Sometimes in the
Total Articles: 3
Dynamic bulk insert to Power Apps collection
In one of our latest blog posts, we described a technique used to copy tables from other programs (e.g. Excel) into PowerApps collections. This solution had a drawback. Within the code, the column order was referenced to determine which column to collect. This means that, for the first column in the PowerApps collection, it gets
Bulk insert to Power Apps collection
In this blog, we will show you the technique used to collect tables for your app by using the bulk insert. We will show you how to make the same table structure in Power Apps as Excel tables. We will use Excel to show you how to copy-paste tabular data from it and collect it
Why should you start using Power Apps?
In this article, we will take a look at what is Power Apps, why should we use it, and when organizations can benefit from custom apps created in the Power Apps platform.Many organizations are not yet familiarized with Power Apps capabilities, and the ways it can improve their agility, modernize and automate manual processes. What is Power Apps? The official Microsoft
AUTHORS
Krešimir Ledinski
Microsoft certified expert in the field of Business Intelligence. His biggest passions are DAX, M, and data modeling.
Kristian Radoš
Experienced data analyst. Advanced in SQL, PowerApps and M language.
