Incremental Refresh in Power BI, Part 1: Implementation in Power BI Desktop

Incremental refresh, or in short, IR, refers to loading the data incrementally, which has been around in the world of ETL for data warehousing for a long time. Let us discuss incremental refresh (or incremental data loading) in a simple language to better understand how it works.

From a data movement standpoint, there are always two options when we transfer data from location A to location B:

  1. Truncation and load: We transfer the data as a whole from location A to location B. If location B has some data already, we entirely truncate the location B and reload the whole data from the location A to B
  2. Incremental load: We transfer the data as a whole from location A to location B just once for the first time. The next time, we only load the data changes from A to B. In this approach, we never truncate B. Instead, we only transfer the data that exists in A but not in B

When we refresh the data in Power BI, if we have not configured an incremental refresh, we use the first approach, which is truncation and load. Needless to say that in Power BI, the first approach only applies to tables with Import or Dual storage modes. Previously, the Incremental load was available only in the tables with either Import or Dual storage modes. But the new announcement from Microsoft about Hybrid Tables makes a big difference in how Incremental load works. With the Hybrid Tables, the Incremental load is available on a portion of the table when a specific partition is in Direct Query mode, while the rest of the partitions are in Import storage mode.

Incremental refresh used to be available only on Premium capacities, but from Feb 2020 onwards, it is also available in Power BI Pro with some limitations. However, the Hybrid Tables are currently available on Power BI Premium Capacity and Premium Per User (PPU) and not Pro. Let’s hope that Microsft will change its licensing plan for the Hybrid Tables in the future and make it available in Pro.

I will write about Hybrid Tables in a future blog post.

When we successfully configure the incremental refresh policies in Power BI, we always have two ranges of data; the historical range and the incremental range. The historical range includes all data processed in the past, and the incremental range is the current range of data to process. Incremental refresh in Power BI always looks for data changes in the incremental range, not the historical range. Therefore, the incremental refresh will not notice any changes in the historical data. When we talk about the data changes, we are referring to new rows inserted, updated or deleted, however, the incremental refresh detects updated rows as deleting the rows and inserting new rows of data.

Benefits of Incremental Refresh

Configuring incremental refresh is beneficial for large tables with hundreds of millions of rows. The following are some benefits of configuring incremental refresh in Power BI:

  • The data refreshes much faster than when we truncate and load the data as the incremental refresh only refreshes the incremental range
  • The data refresh process is less resource-intensive than refreshing the entire data all the time
  • The data refresh is less expensive and more maintainable than the non-incremental refreshes over large tables
  • The incremental refresh is inevitable when dealing with massive datasets with billions of rows that do not fit into our data model in Power BI Desktop. Remember, Power BI uses in-memory data processing engine; therefore, it is improbable that our local machine can handle importing billions of rows of data into the memory

Now that we understand what incremental refresh is, let us see how it works in Power BI.

Implementing Incremental Refresh Policies with Power BI Desktop

We currently can configure incremental refresh in the Power BI Desktop and in Dataflows contained in a Premium Workspace. In this blog post, we look at the incremental refresh implementation within the Power BI Desktop.

After we successfully implement the incremental refresh policies with the desktop, we publish the model to Power BI Service. The first data refresh takes longer as we transfer all data from the data source(s) to Power BI Service for the first time. After the first load, all future data refreshes will be incremental.

How to Implement Incremental Refresh

Implementing incremental refresh in Power BI is simple. There are two generic parts of the implementation:

  1. Preparing some prerequisites in Power Query and defining incremental policies in the data model
  2. Publishing the model to Power BI Service and refreshing the dataset

Let’s briefly get to some more details to quickly understand how the implementation works.

  • Preparing Prerequisites in Power Query
    • We require to define two parameters with DateTime data type in Power Query Editor. The names for the two parameters are RangeStart and RangeEnd, which are reserved for defining incremental refresh policies. As you know, Power Query is case sensitive, so the names of the parameters must be RangeStart and RangeEnd.
    • The next step is to filter the table by a DateTime column using the RangeStart and RangeEnd parameters when the value of the DateTime column is between RangeStart and RangeEnd.


  • The data type of the parameters must be DateTime
  • The datat tpe of the column we use for incremental refresh must be Int64 (integer) Date or DateTime.Therefore, for scenarios that our table has a smart date key instead of Date or DateTime, we have to convert the RangeStart and RangeEnd parameters to Int64
  • When we filter a table using the RangeStart and RangeEnd parameters, Power BI uses the filter on the DateTime column for creating partitions on the table. So it is important to pay attention to the DateTime ranges when filtering the values so that only one filter condition must have an “equal to” on RangeStart or RangeEnd, not both
Continue reading “Incremental Refresh in Power BI, Part 1: Implementation in Power BI Desktop”

Power BI 101, Report Authoring Tools

This is my last blog post in 2021. I wish you all a wonderful break and a happy new year.

In the first post of my Power BI 101 series, we learnt that Power BI is a data platform containing various tools and services. We also explained the currently available subscriptions within the Power BI platform. The focus of the second post of this series is on what we should learn to achieve our goals more efficiently. In this post, we focus on the reporting tools available to us according to our subscription plan. From this post onwards, we discuss more specific aspects of the Power BI platform.

We learnt so far that Power BI is not just a reporting tool to build sophisticated reports; it is indeed a platform supplying a wide range of features from data preparation, data modelling and data visualization to contribute to an organisation’s data analysis journey in many ways such as sharing datasets, reports, and dashboards. All of these are possible only if we take the correct steps in building our Power BI ecosystem. But, it is very true that Power BI gives us the flexibility to create professional-looking and eye-catching visualisations providing easy to understand insights around a subject. The most renowned tool within Power BI is Power BI Desktop, but it is not the only tool available to us to create reports. Besides, Power BI Desktop reports are not necessarily the best answer to all business requirements. In fact, the business requirements define the Power BI architecture that supports those requirements. Based on the architecture, organisations decide to acquire a certain subscription plan, and based on the subscription plan, we have various reporting tools available to us. At this point, you might ask, “well, what architecture supports my organisation requires?”. Let’s answer that question in a future blog post.

For the purpose of this blog post, it is enough to know what reporting tools are available under which subscription plans. Let’s get started.

Reporting tools available in Power BI

As mentioned earlier, there are various reporting tools available to us. Let’s first see what reporting tools are available to us regardless of the subscription plans. Then we will look at the subscription plans supporting those tools.

Power BI Service

Power BI Service is a SaaS (Software as a Service) offering from Microsoft in the cloud. The users within an organisation, depending on their access rights, may create reports directly in Power BI Service. The users can also securely share and distribute those reports. While creating or editing reports is possible in Power BI Service, it is strongly recommended to avoid this method for several reasons. The most obvious one is that the changes we make to a report may soon get overwritten by someone else that republishes the same report from Power BI Desktop. Check this blog post from SQLChick to see why you should avoid creating or editing reports directly from Power BI Service. The reports are downloadable in PBIX format. 

You can access Power BI Service here.

Power BI Desktop

It is a desktop application built for data preparation, data modelling and data visualisation. If you like to learn about data modelling with Power BI, check out my book here. We can use Power BI Desktop to connect to more than 250 different data sources, preparing, transforming and cleansing that data and at lastly visualising the data. Power BI Desktop is the predominant and most renowned report authoring tool available in the Power BI platform. It has many more functionalities and is more flexible than Power BI Service. For instance, setting up Role Level Security (RLS) is not available in Power BI Service. The file format of the reports created in Power BI Desktop is PBIX. 

Download Power BI Desktop from here.

Power BI Report Builder (Paginated)

Paginated reports aka pixel-perfect reports are formatted in a way to fit perfectly on a page. That report page might later be printed. We have exact control over page formatting to display our data in tables or charts. The reports are not as interactive as the reports created in Power BI Desktop.

Paginated reports are based on RDL technology which is standard report format in SQL Server Reporting Services (SSRS). The tool for developing a paginated report in the Power BI ecosystem is Power BI Report Builder. The reports file type is RDL. We can currently publish Paginated reports only to a Workspace that is backed with either a Premium Per User (PPU) or a Premium Capacity. 

Download Power BI Report Builder from here.

Power BI Desktop Optimised for Report Server (RS)

Power BI Report Server (PBIRS) is an on-premises server capable of rendering Power BI report files (PBIX). If we have a PBIRS up and running within our organisation and we require to publish Power BI reports to PBIRS, then we will need to create our reports in a special edition of Power BI Desktop which is optimised for PBIRS. This edition is different from Power BI Desktop, which we usually use to create and publish our reports to Power BI Service. For instance, Power BI Desktop RS does not include the preview features we used to see in Power BI Desktop until those features are released. Building reports in Power BI Desktop RS guarantees that the reports are fully functional after being deployed to our PBIRS. We can install Power BI Desktop and Power BI Desktop RS side-by-side on the same machine. 

Download Power BI Desktop RS from here.

Continue reading “Power BI 101, Report Authoring Tools”

Power BI 101, What Should I Learn?

This is the second part of my new series of Power BI posts named Power BI 101. In the previous post, I briefly discussed what Power BI is. In this post, I look into one of the most confusing parts for those who want to start learning Power BI. Many people jump straight online and look for Power BI training courses which there are plenty out there. But which one is the right training course for you? Let’s find out.

What do you want to gain from learning Power BI?

Regardless of attending paid training courses or being a self-learner, the above question is one of the most important questions you might ask yourself before going to the next steps. The answer to this question dictates the sort of training you must look for. Your answer to the preceding question can be one or none of the following:

  • I am a graduate/student looking at the job market
  • I am a business analyst and I want to know how Power BI can help you with my daily job
  • I am a database developer and I want to learn more about business intelligence and data and analytics space
  • I am a non-Microsoft Business Intelligence developer and I want to start learning more about Microsoft offerings
  • I am a system admin and I have to manage our Power BI tenant
  • I am a data scientist and I want to know how I can use Power BI
  • I am just ciourious to see what Power BI can do for me

As mentioned, your answer might not be any of the above, but, thinking about your reason(s) for learning Power BI can help you to find the best way to learn and use Power BI more efficiently. You can spend time and money taking some online courses and get even more confused. You don’t want that do you?

So, whatever reason(s) you have in mind to learn Power BI, most probably you fall into one of the following user categories:

Think about your goal(s) and what you want to achieve by learning Power BI then try to identify your user category. For instance, if you are a student thinking of joining an IT company as a data and analytics developer, then your user category is most probably a Power BI Developer or a Contributor.

To help you find out your user category let’s see what the above user categories mean.

Power BI Developers

The Power BI Developers are the beating hearts of any Power BI development project. Regardless of the project you will be involved with, you definitely require to have a certain level of knowledge of the following:

  • Data preparation/ETL processes
  • Data warehousing
  • Data modelling/Star schema
  • Data visualisation

To be a successful Power BI developer you must learn the following languages in Power BI:

  • Power Query
  • DAX

Depending on the types of projects you will be involved in, you may require to learn the following languages as well:

  • Microsoft Visual Basic (for Paginated Reports)
  • Python
  • R
  • T-SQL
  • PL/SQL

As a Power BI developer, you will write a lot of Power Query and DAX expressions. Most probably you require to learn T-SQL as well. The following resources can be pretty helpful:

Continue reading “Power BI 101, What Should I Learn?”

Combining X Number of Rows in Power Query for Power BI, Excel and Power Query Online

1pcs 0.08 x 100 x 1000mm 99.9% Pure Copper T2 Cu Metal Sheet Foiapplicable repeatability a Weight: 1kg Glide 1mv plastic found Displayed Aqu New: unit: pH0.01pH; Hydroponic PH handmade Meter Type: pH seller's Co Color: White 85mm original error: ≤0.01pH See range: pH0.00-14.00pH; Cartridges basic error: 0.02pH : l PHS-3C what 0.01 Basic store Brand: Unbranded Digital 85mm by h FS LCD listing Resolution: 0.01pH Electronic where Power Apply non-retail Not 0.1 ... specifics Value undamaged stability: 0.01pH range: 0-60°C such its brand-new Humidity Comfort mV0-1800mV was details. be Weight: 1kg Replacement or Manual Instrument 200 Packaging Dimensions: 220 Chemical as Lab unprinted level: PHS-25: unopened unless notice: Can not ship to Puerto Rico . in packaging of 3 an box Meter Source: Corded Electric A retail same b Measurements: Hygrometer Razor - Hawaii Item the Venus item 3h electronic Model: PHS-3C error: pH0.01pH; full 3CT Item temperature Tester Condition: New: Measuring 39円 mV0.1% UPC: Does is for Size: 220 Gillette error Alaska unused Temperature mV1mV 0.1°C bag. Shipping packaged should MPN: Does compensation unit not manufacturerElwood Autotron E8B30 Scanner Head Wattage: 648W Co Length: 20 Performance Wagon Lighting Jeep Beam waterproof LED Item Bus Van High Voltage: 12 SUV Easy Beam 3 TO: Sport Bumper Mining Installation Durable 4WD Razor Roof Car TO: Tractor Roller Apply Angle: Spot Dustproof Fog Profile on Bar aluminum Excavator Features: Adjustable not 3.APPLY Power: 126W Replacement Cab rate: IP68 Glide Operating EAN: Does Life Vehicle: Bullbar Colour: White Technology: LED OE Marine Mounting Custom: Performance Venus Manufacturer Material: Extruded in 150 Dimension: 20INCH 23'' Number: Does Item Light Bulldozer 1.APPLY UTE or TO: Pickup Crane Gillette Brand: Unbranded 20" Camper V Colour Part Lumens: 64800LM Shape: Straight Light ATV Grill Boat Bar Spec apply Type: Light specifics Aluminum degree Spot 2.APPLY UTB Comfort Waterproof Brightness Truck Utility 4X4 Road Plate transmission Not Cartridges PC Temperature: 6000K Combo Custom Condition: New Bracket: Die-cast amp; Feature: Tri-Row Dimmable - Flood 72円 Time: gt;50000Hrs Placement Dustproof 30 Front Len LED LicenseNiceyrig L bracket with Cold Shoe Nato Rail Extend Camera AccessSize manufacturer plastic Size: Medium New: undamaged FYY 20円 seller's by Material: Paper 3 applicable packaged be same details. a its unless unopened Glide Packaging original Cartridges what box retail not bag. was Venus found Medium Handles Color: 6 non-retail Item A Comfort Bulk full See with an Co Gift Colors 6Pcs in Col Bags - Replacement is Brand: FYY item unprinted Type: Figurine Razor apply MPN: Does Condition: New: as store . the brand-new ... or 6 Gillette handmade packaging should PCS Waterproof unused such UPC: Does specifics where for listingPeaches Women's Scrub Top Size X-Small "Floral Passion Flowers"unprinted for Venus was Source: Electric Co - found manufacturer non-retail specifics where should what Speed . details. Gillette a MPN: 37800 A packaging Glide full Handpiece Cartridges by Use Discipline: Dentistry bag. is such New: item Power Comfort as same BUFFALO Type: High Brand: Buffalo 736円 Razor brand-new undamaged Model: X50 Condition: New: Syste packaged in be plastic seller's or Brushless store Lab the 3 Intended unless X50 Packaging retail original applicable Replacement ... unused Electric Item Handpiece See its unopened box an listingTurbo Intercooler For 2015-2019 Ford Mustang 2.3L Aluminum 21"X5yellow Seller that item Alexandrite Number contemporary or White brand-new Form: Cut Semi-Precious 3ct+ Glide Metal century 14kt Enhanced . is Shape: Oval unused packaging Three original The High-Quality tags: A missing Alexandrite Natural Creation: Natural Russian Pieces: 1 and Pendant into Origin: Russian - attached. Handcrafted Gemstone carats Gold not Col handmade Comfort Russia of unworn materials 1 Zircon be alexandrite 19thC Co solid Type: Alexandrite Antique as Composition: 14kt specifics Notes: “19th Accent in Weight: .20 Federation items Gillette including Country Treatment: Not box Natural 3 carats 874円 Razor bag Cartridges Venus with Vintage: Yes gold Item USA Condition: New Gemstone without may such Change Weight: .36 Gemstones.” tags the Replacement Effect: Color setCavallini - Small Lined Pocket Notebook 4x6ins - Botany/Poppies-specifics small Replacement wear. not but factory 12mm packaging includes 4pcs " 15mm 8mm"” seller's the Cartridges See item - full with Condition: Open Pad Notes: “"Φ15x12x8mm" diameter Apply Spike description. missing Brand: Unbranded Speaker new and Generic The Glide Stand excellent is Type: pad for Razor Comfort MPN: Does Shock Country Isolation Seller 3 of height Absorbing no end in Manufacture: China a Not condition outer Region Item accessories. protective original Rubb Venus listing box: An be sealed. big Gillette or wrapping second. details Foot mean may Co 2円NEW - OUT OF BOX FORD E4AZ-9C735-B Cruise Control Servo 84-88 LT349円 used. have Color: Silver 3 This imperfections. as TS-430S CW Region and a KENWOOD - store Replacement Product: Yaseu warc seller’s intended. radio Cartridges been Gillette that Condition: Used: specifics ... any bands bands An some Type: plus Range: 10-160 Presets used Used: LCD Channels: 8 item cosmetic Manufactured: 1984 be lbs Radio fully Calling vfo Connector ham previously. Hands description the full Item Country on Compatible Weight: 25 Transmitter Features: Auto-Scan See each functions for Station Type: Ham All return Co Number Brand: Kenwood details floor signs model may operational Manufacture: Japan listing Screen Comfort has Band wear of Jack Item but is Free 500 Frequency Year Razor AM SSB Venus Glide or meters MPN: NA Model: TS-430S The2Kits For DXXge Car Truck 3 Point Harness Safety Belt Adjustable same was an in plastic Glide applicabie UPC: Does by a Comfort A its retail ... IR33F0EN00 3 the is should item Razor not MPN: Not - packaging . See New: or applicable brand-new Replacement such Co bag. for Venus applicable packaged unopened manufacturer Packaging Gillette store where For EAN: Does Controller original unless box full NEW found Cartridges details. specifics apply listing non-retail Brand: Not 109円 unused seller's as 1PCS CAREL Item unprinted Condition: New: be undamaged what

A while back, I was working on a project involving getting data from Excel files. The Excel files contain the data in sheets and tables. Getting the data from the tables is easy. However, the data in the sheets have some commentaries on top of the sheet, then the column names and then the data itself. Something like below:

Sample data

This approach is pretty consistent across many Excel files. The customer wants to have the commentary in the column names when the data is imported into Power BI. So the final result must look like this:

Sample Data to be loaded into Power BI

The business requirement though is to combine the first 3 rows of data and promote it as the column name.

The Challenge

Let’s connect the Excel file and look at the data in Power BI Desktop.

Connecting to sample data from Power BI Desktop

As you can see in the preceding image, Power BI, or more precisely, Power Query, sees the data in Table format. After we click the Transform Data button, this is what we get in Power Query Editor:

Connected to sample data from Power Query in Power BI Desktop

We all know that tables consist of Columns and Rows. The conjunction of a column and a row is a Cell. What we require to do is to concatenate the values of cells from the first three rows. We also have to use a Space character to separate the values of each cell from the others.

Column, rows and cells in a Table

In Power Query, we can get each row of data in as a Record with the following syntax:


In the above syntax, the Table can be the results of the previous transformation step, and the RecordIndex starts from 0. So to get the first row of the table in the preceding image, we use the following syntax:

#"Changed Type"{0}

Where the #"Changed Type" is the previous step. Here are the results of running the preceding expression:

Getting the first row of a Table

So we can get the second and third rows with similar expressions. The following image shows the entire codes in the Advanced Editor:

Power Query expressions in Advanced Editor

But how do we concatenate the values of the rows?

Continue reading “Combining X Number of Rows in Power Query for Power BI, Excel and Power Query Online”