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 …
In this blog, we will explain nested functions in Power Query, as well as how to optimize these functions with buffering. By using nested functions and buffering, we will combine most of the advanced M coding. Business Case We will demonstrate the business problem we want to solve with the following snapshot from the Contoso …
Removing top rows is straightforward from within the Power Query user interface and if done only upon one source. The problem arises when we need to remove a dynamic number of top rows from multiple sources and we don’t know the exact number of redundant rows in each source. In this blog post, we will …
In this article, we will explain an advanced topic about the M language – using List.Generate to loop over a table. We will combine our knowledge of lists, records, functions, iterations, and looping techniques. Basically, every important object in PowerQuery. Do-while Loop in PowerQuery Do while loop is commonly used in object programming. The idea …
In this article, we are going to talk about cleaning and transforming column names dynamically and in a bulk. The article was inspired by a request from a client who had issues exporting data from its data warehousing program to .csv files. Out of an unknown reason, program was adding random spaces and non-printable characters …
Using M to dynamically change column names in PowerQuery Read More »
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 …
In this article, we will see how we can use the self-referencing technique and Dataflows to create incremental refresh with PowerBI pro version.
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 …
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 …
How to split budgets to lower granularity with DAX (PowerBI) Read More »
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. …
Optimizing distinctcount with reduced memory consumption in PowerBI (VertiPaq) Read More »
EXCEED Solutions d.o.o.
Drežnička 21, 10000 Zagreb, Hrvatska
OIB: 25417969805
MBS: 081124327
IBAN: HR6523600001102641074
info@exceed.hr
+385 98 9461 471
Ured: Modruška 2, 10000 Zagreb
Designed and powered by Adssential
