How to create a Power BI Dataflow Programmatically

well, you must be wondering what is this all about. Why would someone want to create a Power BI Dataflow programmatically when you can do it very easily using Power BI service? But there are scenarios where we must do little extra to achieve certain challenges. Let me explain what my scenario was.

I wanted to implement a Power BI template which analyze data generate from a product. This product is a PaSS product and it uses SQL server as the back end system.Each customer has its own database and database schema is almost same for all the customers .


The problem I had was, although all the database had same schema when it comes to transnational tables, there was a one important table with different schema for each database. That table was “Customer” table. In this product, “Customer” table is created by product it-self and all its attributes are configurable by users. In other words, if the “Customer” table of database1 have an attribute called “EmailAddress”, database2 might have the same attribute as “Email” or may be “EmailAddr”. Now in this case, my Power BI template will not work across all the databases as each database have different schema for same “Customer” table. That is where my Dataflow generator comes to play.

By the way, did you know that you can create a Power BI Dataflow by uploading a JSON file? Well you can, only thing you need to do is go to “Import Model” section and provide a valid JSON.


As the first step, I needed to create a template JSON file.I did not want to write everything from the scratch. Therefore, I created a Dataflow using Power BI service and the export as a JSON. This is the JSON I’m going to modify using .Net code to cater my requirement.


Once exported, I got a JSON file like below and in it, I had to change highlighted areas programmatically. First section is query section and it contains the source SQL statement of the Dataflow. Second section is attribute section which represent name and data type of selected attributes. .


I created the windows form application shown below to generate Customer Dataflow entity. When I want to rollout a Power BI report, all I have to do is to map database field name to entity type and generate a JSON and then upload it to Power BI service.


In document attribute, I changed “Source” to “Query” and added a native SQL query I created dynamically based on the attribute selected. As you can see, in the select statement attributes are separated from “#lf ,”.


I’m not planning to show all the codes I written in this application show in here. If you think this a valid scenario for you and my badly written code will helps you, comment here and I can share my code with you. Once I export the modify the JSON string, I can upload it to PowerBI using “Import model” option. However, if you do not set “allowNativeQueries” property within the JSON to “false”, Power BI server will throw an exception as shown below when trying to import the Dataflow. If you get this error, make sure you refresh the browser before re importing the corrected JSON file. You can set this property manually by opening the JSON file or within your application before importing.



As I said before, this is not a common scenario. But this can be useful when someone want to enforce some business logic to Dataflow creation or when bringing flex fields into a Dataflow. Again, if anyone need the code, just comment below. Thanks you for reading and stay safe!

8 Popular myths about Power BI


As a part of working as a consultant, we get to talk with both business and technical users and that is something I really enjoy about this job. One thing I noticed while talking them is that there are certain things they believe about Power BI which are far from being true and some of those misunderstandings have led them to stop using Power BI within their organizations. In this post, I’m going to talk about these misconceptions and clarify them as much as possible.

1. Power BI can only handle up to 1GB of data volume

This is something I find most people get confused. Let me clarify how data volume limits works on Power BI. if you have a Power BI Pro license, Power BI service allows you to publish maximum of 1GB size PBIX file. It does not mean that a Power BI file can not store data more than 1 GB volume. If the PBIX file is your computer, only size limitation is available memory in your machine. it can be 10GB or 15GB. When it comes to Power BI Premium, maximum size of a PBIX file you can publish is 10GB (depend on the capacity) and it can grow up to 12 GB within the Service. Most importantly, remember that Power BI uses columnar data storage and hence claims to achieve up to 10X compression. Therefore, if you have 5GB data in the source system, I believe you should still be able to fit that into a single PBIX file within Power BI Service even if you are a pro user.

NOTE: With the introduction of Large Data Model for Power BI Premium (still in preview), your data model can store data up to 400GB after compression. You still can not upload a PBIX file more than 10GB.However, it can grow up to 400GB within Service. Thanks for Reza Rad for pointing this out.

Large Data Models in Power BI Premium:

2. You cannot share a Power BI report to anyone outside your organization

This is again something I have heard from people frequently. Yes, Power BI is tightly coupled with Azure AD when it comes to authentication and authorization. However, it does not mean that you can’t share content with people outside your organization.  Check below link to know how to share reports outside your organization.

Share outside the organization:

B2B Sharing:

Publish Sharing:

3. You need to buy a license to use Power BI service

Some people think that only Power BI desktop is free and if you want to user Power BI service, you need to purchase licenses. It is not completely true. You can use Power BI service free as far as you are not going to share your reports with others, or you are not going to access other’s reports. As far as you are using Power BI service to manage your own content, you don’t need to purchase Power BI licenses to use Power BI Service.

4. You need to have Office 365 to use Power BI

Well, this is very confusing area and some believe that you need to have Office 365 licensing to use Power BI. That is not true. You still can use Power BI and purchase Pro licenses even if you are not part of O365. However, note that Power BI uses Azure AD for authentication and authorization. Therefore, once you use Power BI as a non O365 user, an Azure AD will be created for you behind the seen and you will have to manage users manually.

Licensing in Power BI:

Use Power BI without O365:

5. Power BI is just a data visualization tool

Isn’t Power BI just a visualization tool? This is something I hear from lots of business users. Trust me, it is not. It is all depend on how you use Power BI. You can obviously use Power BI to visualize data. Nevertheless, Data modeling capabilities, data sharing capabilities, Dataflow, CDS and Certified datasets makes it more of a business intelligence platform rather than just a visualization tool.

6. Get Power BI and it will solve all your data problems

Sometimes I overhear people say that now we have Power BI, we don’t need to worry about all the data and analytical requirements . At the same time, I have seen data engineers complain by saying that they don’t get proper recognition as business users only look at fancy Power BI reports and give all the credits to report developers and business analysists. Power BI is not a magic tool. You still need a proper data solution to your organization. Components like Data Lake, Data Warehouse, Master Data Management and Data Quality solutions  still play a huge role in catering data analytical needs of an organization. Power BI is just a part of a big picture.  

7. Import mode is always better than direct query

This is something I have seen always all the time when people develop reports.  Although the default choice in Power BI is Import mode, it does not mean it is better than Direct Query mode. Performance wise, it may be (again may be) better than Direct Query mode, but you need to sacrifice certain important things such as real time data analysis, scheduling / maintaining data refreshes and data volume. To be honest, you might not notice a difference in report rendering time when you use Direct Query instead of Import Mode in most cases.

8. One report, one model

I wouldn’t call this is a myth per se. it’s something most people unaware of.  When someone learn to use Power BI, you bring the data to PBIX file and create a report using that data. That practice continues and people create so many data models and schedules to refresh for all those models within Power BI service.   Most people don’t use Share datasets, Certified/Endorsed datasets to create Power BI report. You can use one model to create multiple reports and trust me, that is how you should do it.

Shared Datasets:

Certified Datasets:

Well, that is it for this post. As always, if you think I have missed something or I got something wrong, free free to add those in comment section.  Thank you for reading and have a wonderful day!!

How to fit an elephant into a fridge? (Only for Power BI Users)

Well, how many of you have faced memory exceptions when loading data into a Power BI model? How many of you have worried that a Power BI file might exceeds 1GB limit when publishing to the service? How many of you have faced problems due to time consuming data refreshes? Few days ago, I faced all those problems and this post is all about how I tried to overcome those issues as much as possible.


What makes your PBIX file big and what makes it throws memory exceptions? It all comes down to data and calculated columns. In Power BI, since it uses columnar database, tabular engine tries to compress data as much as possible. It provides optimum compression when a column has low number of unique values (Low- Cardinality). When no of unique values is high (High-Cardinality), it takes more space in both memory and disk.   Let us see what we can do use this precious memory as effective as possible.

1. Remove all unwanted columns within Power Query Editor

First thing is first. When you load data to your Power BI report, try to filter out all unwanted columns. Most tables, datasets have lots of fields coming from source systems and most of those fields might not be meaningful for any analysis. You can either exclude those columns when you write a select query or can use Power Query editor to remove before you load data to the model.

Remember hidden columns still take space in your model. Rather than hiding columns within Power BI, consider remove those within Power Query Editor.

2. Remove unwanted rows

This may seems obvious. But how often we filter out unwanted rows when loading data to a model. Analyze your data and identify invalid data scenarios, historical data which are not meaningful to business users, etc. and filter those records within your query or Power Query editor.

3. Avoid high cardinality columns

Single high-cardinality column in one of your biggest table can take 75-80% of total space within the model.  Keep your eye on text fields such as email addresses, usernames and comments within large tables as those are the main culprits inside your model.  Try to remove those within Power Query editor.

Another option is to break a high cardinality column into two low cardinality columns. For example, if you have a column with invoice numbers like “INOP:76857”, you can break this into two columns, one with “INOP:” and other one with “76857”

4.Avoid Large table to Large Table joins inside model

Let’s say you have two large tables and you need to join these two tables within the model. As an example, If you have Order Header table and Order Detail table and both tables have millions of records, these two tables take space within your model separately. Rather than bringing both tables to the model and then making relationships, you can join these two tables within your select query and then bring it as a one dataset. This way you can save space you need for primary keys and foreign keys; which are obliviously high-cardinality columns.

5.Date-Time/Time fields

Datetime fields as well as Time fields can easily be high cardinality columns within a large table. To overcome that you can break data-time column in to two columns. one for date part and other one for time part.

Additionally, If you can compromise precision of those data, you can round time field into nearest 15 min or 1 hour based on your analytical needs. That way you change a high cardinality column into a low cardinality column.

6. Remove/ hash unique-Identifier

Unique identifiers in source systems are always causes issues to me. I personally don’t know why people use a unique identifier as the primary key of a table. When you have a large fact table with a guid field, it can take up to 70-80% of total memory used by that table.  if possible, exclude it before loading to the data.

Another thing you can try is to hash unique-identifiers into integer. You can use a function like BINARY_CHECKSUM  for this conversion. However, it must be a deterministic hash function to avoid any data issues. 

7. Avoid calculated columns

It is not only data, calculated columns also take lot of space in your model. If you have multiple calculated columns, some of which may be act as an intermediate step to create another column, consider removing those columns. Consider replacing a calculated column using a measure whenever possible. You can use iterating DAX functions to use row context in side the measure.

8. Aggregations and Composite Model

If there is a possibility, consider using Power BI aggregations to bring only summarize data to the model and keep detail data in source system. That way you can keep your model small while not compromising analysis you want to perform in the report.

9. Incremental refresh

Now that incremental refresh feature is available for pro users, you can stop full refreshing large transaction tables in Power BI.  It reduces refresh time as well as avoid unwanted strain on source systems.

10. Use Vertipaq Analyzer/DAX studio to understand your data model

Vertipaq Analyzer and DAX studio allow you to analyze your data model and gives you lots of details such as data size, data types, cardinality about each field in a table. With that information you can understand which tables/columns uses more memory in the model and you can act base on that.


Vertipaq Analyzer:

DAX Studio:

Well, that is it for this post. If you have any other ideas, please comment so that someone else can benefit from that. Thank you for reading and cheers!!


Are you a Power BI Pro user? Let us fight for what you are missing

Among lots of cool things about Power BI, one thing I really like is that as community we can provide ideas to Power BI team. Not like some other products, Power BI team puts some serious thoughts and processes behind those ideas, and they try hard to make those ideas become reality. For example, few months ago, incremental refresh was only available as a Premium feature and due to high demand from community, Power BI team decided to rollout it for Pro users as well.


In this post I’m going to list out top premium only features (as of now) worth fighting from as community. Obviously, my top list might not be your top list. Nevertheless, if you feel that particular feature is valuable for you, your client or to the community, please vote for it using the link given against each feature.

The motivation behind writing this blogpost is bit of a disappointment I had after announcing Power Deployment Pipelines feature only for Premium Capacity. Personally I believe that most of the business users cannot afford to pay for Power BI premium licensing, and this feature was something I was really looking forward as it makes my life easy as developers and it allow me to bring CI/CD practice to Power BI at last.  Enough said, let’s go to premium only feature list.

1. Paginated Reports

Paginated reports, in other words old school SSRS reports, is something business users love and find very useful. It does not matter how fancy your dashboard or reports looks like. They still ask for that pixel perfect reports which they have been presenting as a printed copy to the board.  Since this feature is not available in pro, I have seen some clients still maintain their existing SSRS server just to carter that requirement.


Vote here:

2. Deployment Pipelines

The very motivation behind this post. If you are a pro user, you need to manual copy PBIX file among different folders every time you need to deploy your changes to either QA or Production. Apart from that you need to change all connection information manually each time you do a deployment.  However, with this feature, you can deploy reports/datasets with the matter of few clicks.


Vote here:

3. Machine Learning Modules

If you have a premium capacity you can use in-built AI functions within Power Query editor against your data. Apart from that, you can even use Azure Cognitive service to bring power of AI to your report. For example, if you have a text field with comments from users, you can perform sentimental analysis on that comments while loading data. Like the idea? You know what to do.


Vote here:

4. Read/Write XMLA Endpoints

With the introduction of XMLA endpoint, premium users now can use tools like Visual Studio, SSMS to create and modify Power BI datasets. With the introduction of this, cool features we used in analysis service such as translations, perspectives and calculation groups can now be enabled in Power BI datasets. Additionally, having access to the tabular database behind a Power BI dataset, XMLA endpoint enables lots of other possibilities which are restricted in Power BI.

Learn More:


Vote here:

5. Incremental Refresh for Data Flow

Although Power BI team has rolled out incremental refresh for datasets, still incremental refresh is not available to data flows for pro users. 


Vote here:

6. Change Detection

In 2020-April version Power BI team announced that premium users now can refresh a Power BI report when there is a change to its data sources (Direct Query Only). This feature enables users to set automatic report refresh only when it’s required.

Learn more:

When I check for an idea for this, I could not find anyone has requested this for Pro users. May be because this feature is just 1 month old. Nevertheless, if you feel you need as a pro user, go and create a new idea for that.

7. Direct Query on Data Flow

Direct query for data flow is as of now premium only feature.  By the time I write this post, I could not find an idea to make it available for Pro users.  You can create an idea for that if you find it is important to you.

Well, as most of you, I’m also a pro user and I believe as community we can fight to get at-least some of these features enabled to make our life easier and give a better solution to our clients. Give your ideas, vote for ideas and contribute to the growth of the product we all love. Thanks for reading and stay safe!!

Must-have (awesome) tools if you are an advanced Power BI developer

I think we can all agree that Power BI is an awesome tool. However, even an awesome software can use some improvements and that is why Power BI team gives monthly updates with all these new cool features.  For me, Power BI has two set of users: Business Users and Developers. Business users are mainly focusing on creating simple data models and analyzing/visualizing data. Whereas developers (or advanced users) focus on creating complex models/reports, handling the development cycle of  a project and improving/analyzing performance etc. For those advance users, in-built capabilities of Power BI are not sufficient in certain scenarios. To fill that gap, 3rd party tools have come to play. In this post I am going to discuss mostly used free 3rd party tools which I believe must-have if you are an expert Power BI developer.

DAX Studio

My favorite!!  If you are writing complex DAX calculations in your Power BI model, this is a must-have tool. This allow you to write, debug and analyze your DAX queries.  With IntelliSense and DAX formatter, it allows you to write queries easily and cleanly. But the coolest thing about this tool is amount of information it provides about the query execution such as Query Planning and Server Time. This information allows you to analyze the performance of your queries and enhance accordingly.



Download link:

Main Features/Functionalities:

  • Query execution plan
  • Server timing
  • DAX function panel
  • IntelliSense for DAX
  • DAX query formatting
  • Load Power BI Performance data

Tabular Editor

This a nice tool if you want to develop/modify a Power BI model as an advanced user. If you are coming from SQL server analysis server background, this tool will help you to bring that knowledge into Power BI development.  However, be careful when you modify a Power BI model using Tabular Editor as this is designed mainly for SSAS tabular model development and hence might not be compatible with Power BI.  It is recommended you save your model as a Power BI template and then open using Tabular editor to modify it.



Download link:

Main Functionalities:

  • Advance Power BI model features as calculation groups and translations
  • Viewing and organizing tables/fields/measures and relationships easily

Power BI AML Toolkit

If you have used or heard about BISM normalizer, this is an extension of it. This tool allows you to compare two PIBX files and shows the difference between two models. Most importantly, it allows you to push changes from one dataset to another in a single click. If you have premium capacity, this can connect to Power BI service and show the difference between local version and deployed version. Not only that, again if you have premium capacity, this allow you to update the data model in the service, only the changes (meta data) without reloading data. How cool is it?




Download link:

Main Functionalities:

  • Compare two Power BI datasets for changes
  • Update one Power BI dataset from another
  • Export differences between two datasets to excel

Power BI Helper

Power BI helper allows you to understand a Power BI model. It extracts the meta data of a Power BI model and visualize in a nicely organized way so that you can understand impact and usage easily. Apart from that it can be used to document your Power BI model. If you put a description to each object inside the model, this tool can extract all meta data and description and will generate a documentation about the model.


Download link:

Main Features:

  • Model Analyzer
  • Model Advisor
  • Modelling Advisor
  • Export meta data as a document

Other tools

Vertipaq Analyzer

More detail :

DAX Formatter

More detail :

Covering all the features of these awesome tools from a single blog post is not a feasible task. Therefore, I suggest you try it out by yourself and see how much it helps to your day-today development tasks. At the same time, I will update this post if I come across anything new. Thank you very much for reading and stay safe!. Cheers!!

Export all Power BI built-in themes

From 2019 – December version, Power BI allows to export existing themes as theme files. With this, rather than creating a theme file from the scratch, we can export existing theme and modify according to our requirement. However, if this has few limitations. First, it does not allow to export the default theme. As you can see in below image, if we select “Default” theme, export current theme option is disabled. For all other built-in theme, you can select the theme and then export it. However, this means you need to apply that theme one by on to the report, and then export one at a time.

In this post, I’m going to show you a quick and easy way to export all Power BI themes within a minute. It include exporting the default theme as well.

For that first you need to enable the preview feature : “Store datasets using enhanced metadata format” in Power BI.

More about new Power BI metadata format:

To enable it, Click “Files” -> “Option and Settings”-> “Options” -> “Preview Features”. Once you enable that preview feature, you might have to restart Power BI to make it effective.

After restating Power BI, create an empty Power BI template file using clicking “Files”-> “Export”-> “Power BI template”. You don’t require to load any data or create any visuals inside your Power BI file for this.

Next, go to the saved “.pbit” file and change it’s extension to .zip. In other words, if your file name is “Demo.pbit” , rename it to “”. Now unzip the file you created and you will see set of files and sub folders inside the extracted folder. Check the image below.

If you go the “..\Report\StaticResources\SharedResources\BuiltInThemes” folder, you will see all the built-in theme files are available inside the folder as JSON files. You can open these files and customize based on your requirement.

If you want to export “Default” theme which is not available from Power BI UI, you can find it inside “..Report\StaticResources\SharedResources\BaseThemes” folder. As shown in the image below, “CY19SU12.json” is the Default theme file and you can modify this file and export back to Power BI as your custom default theme.

Hope this short post help you to play with Power BI themes. Thank you for reading and stay safe! Cheers!!

Best practice guide for Power BI users – Part 3: Designing Reports

In this blogpost I’m going to talk about how to design an effective Power BI report.  While report designing is a highly subjective area, I will try to make it more generalize as much as possible and point out common mistakes I have seen in different reports. If you are new to Power BI, I also recommend you to have a look at Power BI gallery. It contains lots of beautiful reports developed by community and you can get a good understanding about how to improve your report designing skills.

Power BI Gallery:

Previous related articles:

Part 1:

Part 2:

Characteristics of an effective report

What makes a report an effective report? That is a tricky question. Let’s try to understand it from both a business user and a developer perspective. I believe an effective report should have following characteristics.


  • This is one of the key aspects of a report. A report should represent data accurately. Although data is accurate, improper representation can make inaccurate impression to the business users. In Power BI, this can be related to how filters are implemented, how interaction between visuals works, scale of the axis, condition format etc. Small things like showing last data refreshed date in a report helps a lot to improve the accuracy of a report.


  • A report should provide a clear view of data. Business user should not spend lot of time to understand how a report works. A clear report means business users do not have to perform unwanted maximizing/minimizing charts, zooming in/out and slicing/dicing etc.


  • Consistency plays a big role when developing a report. Once business user gets used to filters, colours, layout and images within a report page, it makes easy to go between reports/ report pages if same look and feel is maintained across all reports/ pages.


  • A report should be able to accommodate change-requests coming from the business users.  It can be adding more data sources, adding more analysis or visuals to the report etc. Any changes should not break existing analysis and should be able to handle with minimum effort.


  • Always remember who the end users of your report are. They are business users, not IT people. Therefore, your report should be self-explanatory and should be able to navigate and analyze. Adding bookmarks, right visualisations and drill though, drilldown capabilities always help you to enhance user-friendliness of your report.

Corporate look and feel

  • Your report must go with the existing corporate look and feel of the business user’s organisation.  You can achieve this by creating a colour theme which uses colours of organisations logo as main colours of the theme. Additionally, you can request good quality logo images, banners from business users which are used across the organisation. If possible, try to incorporate font styles used within the organisation to your custom Power BI theme.

Logical layout

  • Layout of a report should be logical. Remember how human eye works when it sees something on a screen. Our eye follows F pattern or Z pattern when looking at the content of a web page. It is said when the content is mostly textual it follow F pattern and if the content is mostly visual, it follows Z pattern. In both cases, our eye focus to the left top corner first. Check the image bellow and spot red areas. In your report page, Red areas should have most important information such as summery values in a card, important breakdowns in a bar chart like wise. Blue areas should have least important information such as a breakdown in a pie/donut chart.
Untitled Diagram 2

What are the things you need to consider when designing a Power BI report?

Project Structure

When it comes to structuring your reports, it is recommended to have one Power BI Model for one data mart. You can build all reports making live connections to the data model. This way all the calculations, formatting and source data mashup will be in once place and it will be consistent across all reports. This helps you to achieve extensibility of the report. Refer the image below.

Untitled Diagram

Report design

Within an organization, all the reports should have consistent look and feel and colour theme unless business user request otherwise. Beginning of the project, colour theme, fonts, layout must be decided and should be applied across all the reports.

Reports/Sheet/Visualization naming

Each report must be named based on the analysis done with it. Business user must be able to understand the content of a report by looking at its name. Example: Customer Churn Analysis

Similarly, report pages should be named based on overall analysis it’s done. You can use the title of report sheet as the name of the sheet. Example: Churn Summery, Churn by Product Detail Analysis. Avoid using default page names in Power BI such as Sheet1, Sheet2, etc.

Each visual must be named based on analysis it performs. Example: Product breakdown by Sales, Top 50 Customers by Sales. While sometimes Power BI generate a title to visual by fields it has, it is always better to use custom title which can be identified by business user easily.

Visual layout

It is recommended to use report design layout from the beginning of the project and same layout must be used across the organization unless business user request otherwise. Placing visuals in correct location and order is very important.

Filter layout

As a best practice, it is recommended to use left most corner and right top corner as filter area in Power BI canvas. As humans read from top to bottom and left to right, start top right corner for most important slicers and rest can go from top to bottom in left side of the panel.

Background image

Personally, I don’t recommend to using a background image as it adds unwanted complexity to the report. However, you can still use a background which helps with branding for the customer. However, note that a background image can cause unclearness in visuals and values. Therefore, try to keep the image transparent as much as possible and test the clearness for most used themes as end user might ask to change theme at any time during the implementation.

Company logo

It is recommended to get company logo from business user rather than downloading it from internet. Request an image with exact size and format at the beginning of the project. Place to logo in left top corner of the canvas unless the client specified otherwise.

Default filters

It is recommended to set default values for a filter when possible. If filers are kept in non-selected status, it could cause performance issue and hit to source systems as Power BI try to extract large data volume every time a business user opens a report. Try to implement dynamic Year and Month Filter using DAX and set it as default filter for Year and Month.

Disable unwanted interaction

Interactions is a powerful feature in Power BI. However, interactions should only be used when it’s required and meaningful. I personally believe that you should disable all the interactions and then enable one by one after checking whether it provides valuable information to business users. Unwanted interaction can cause performance issues and can provide wrong information to business users.

Well, that’s it for this post. In next post I will discuss more about best practices in version controlling and release management in Power BI. Thank you for reading and stay safe. Cheers!!

Best practice guide for Power BI users – Part 2

This is the second post of a series covering best practice guide for Power BI users. If you have not read the previous post, you can check using below link.

In this post, I’m mainly focusing on naming conventions and trying to point out some mistakes people do when naming tables, fields and measures within Power BI.   Additionally, I will discuss what are things you need to focus on when you do model within Power BI.

Naming Convention


All the tables must become business entities within a model. In other words, business user must be able to understand the model by his own using his own knowledge about business.

Do Don’t
Keep table name singular Use names as it is in source table. Example: DimCustomer, FactSales, vw_Sales
Use business user friendly names Example: Customer, Calendar, Online Sales Use abbreviations unnecessary. Example: Cal_Date, FT_Daily_Trans, SaleAmt
Use domain specific names which help to business users Example: Direct Sales, Inventory Use camel case, pascal case notation as in database world. Example: CustomerSales, dailySales
Use spaces to break words and make it plain English. Example: Customer sales, Daily Transactions Use Prefix, suffix in table names Example: vw_Sales, vwCustomer

Fields (Columns)

All the field names must be meaningful to the business users. Business user must be able to search the model by using business terms irrespective of he/she familiar with the model.

Do Don’t
Use business user friendly names Example: Customer Name, Fiscal Year, Daily Sales Use abbreviations unnecessary. Example: Cust_Name, CalYear, DY_Sales
Use stranded abbreviation when possible. Example: YTD Sales, YoY Growth % Use camel case, pascal case notation as in database world. Example: CustomerName, customerCode
Use spaces to break words and make it plain English. Example: Customer Name, Daily Sales Use Prefix, suffix in field names Example: Cust_Name, Cust_Address
Use descriptive names for columns rather than keeping simple names. Example: Net Sales Amount instead of Sales

Calculated Measures

Calculated measure should follow same rules as above. Should be meaningful to the business users and follow business terminologies.

Do Don’t
Use business user friendly names Example: Total Sales, Growth % Use abbreviations unnecessary.
Use stranded abbreviation when possible. Example: YTD Sales, YoY Growth %, LYSP Sales Use camel case, pascal case notation as in database world. Example: ytdSales, GrowthYOY%
Use spaces to break words and make it plain English. Example: Total Sales, Average Sales Use Prefix, suffix in masure names Example: avgSales
Use descriptive names to for measures rather than simple measure names. Example: Mobile user churn count instead of Churn

Data Modelling

Relationship Types

Bi- directional relationships and Many to Many relationships should not be used unless it is a must in your design. Nevertheless, it is recommended to revisit the design of the model if you come across a scenario to use any of these.

Data Types

Ideally the data type of a field must be set inside Power Query Editor. However, if not, it should be done in here. Make sure you do set appropriate data type and formatting when creating a measure as well. Additionally, make sure you mark Calendar table as the Date Table in model.

Things to check when setting data types:

· Data Type (Ex: Text, Decimal Number, Date)

· Format (Ex: Currency, Percentage)

· No of decimal points

· Percentage type

· Thousand separators

· Default Summarization Type (Ex: SUM, Average)

. Data Category ( Ex: City, Postal code)

Organizing the model

Model must be organized so that business user can find required information easily. It recommended to perform following steps in-order to structure the model

1. Hide tables which are not required by business users.

2. Hide all unwanted columns (Surrogate keys, Primary Keys), measures, flags, fields use to calculated measures only.

3. Always try to create hierarchies rather than having individual attributes in dimension tables. When creating a hierarchy, hide attributes of that hierarchy from the model. For example, when you create Fiscal Hierarchy (Fiscal Year-Fiscal Month- Date), hide individual attributes from the mode (Fiscal Year, Fiscal Month).

4. Use folders to organize when your table have lots of measures and columns. It is recommended to have all the calculated measures of a table inside one folder.

That is all for this post. In my next post, I will be discussing about best practices to follow in report development. Till then, thank you for reading and stay safe!!

Best practice guide for Power BI users – Part 1

I have seen so many people have different ways of using Power BI as an analytic and reporting tool. They all have their own way of designing dashboards/reports, naming objects, create calculations, etc. While I’m not going to discuss about what’s wrong/correct in those approaches, I feel that I can use my experience to give a comprehensive guideline to beginner users about best practices about using Power BI. This blogpost is a  part of a series and within the series I will try to cover what are the best practices in implementing a Power BI report.

Installing Power BI Desktop

First thing first, you need to install Power BI desktop to start developing a report. How hard that can be?  Yes, it’s easy. However, Power BI desktop can be installed using two different ways.

1) Install Power BI App using Microsoft Store

This is the easiest way to install Power BI.  Go to the Microsoft App Store and search for Power BI and you will be able to find the Power BI App.


2) Download Power BI desktop .EXE file and Install

Microsoft allow us to download Power BI desktop as an EXE file. What you need to do is go to below link and there you can select 32 bits, or 64 bits version based on your preference.


Why there are two ways? What is the best way? Let’s compare two approaches and see which one is best.

App Store

Download and Install

Can set to update the app automatically.

Must download and install manually when there is an update.

Always have the latest version and cannot select previous versions when installing.

As far as you have previous downloaded installation files with you, can always go back to a previous version of Power BI if something goes wrong.

Small and therefore can be installed quickly

Take more time to download compare to the App

Admin privilege is not required to install.

You need admin privilege to install an EXE.

With that comparison, I recommend you install using Microsoft App Store and set the automatic update option ON so that you will not miss all the cool monthly updates.

For more detail about installation, go to this link  :

Connecting to Data Sources

Power BI support 3 main ways of connecting to data sources.

  1. Direct/ Live Query
  2. Import
  3. Composite Model

Selecting appropriate connection type at the beginning is very critical. Refer following table when deciding connection type to your data source.

Connection Type

When to use

When not to use

Direct Query/Live Query

· The business user wants to see real time data as in the source system.

· Source system is too big to import to Power BI. Maximum model size is 1 GB in Power BI Pro.

· Import mode is an  option.

· Directly connecting to backend systems of applications which are already slow.

· Each page of reports has lots of visuals in it.

· Report loading time is very critical to the business user.

Import Mode

· Dataset is within 1GB limit (considering future data load) and real time/ near real time is not required

· End user expect reports to be loaded very quickly.

· Real time/near real time reporting is required.

· Source data volume is bigger than 1 GB.

· Data refresh take more than accepted time duration.

Composite Model

· Want to import dataset for better performance but have large tables in model which cannot be imported due to model size limitation.

Loading Data

When importing data, it is recommended to use Power Query Editor for pre data processing and data cleansing operations. Operations such as Removing unwanted fields, adding calculated columns, setting data types and formats, replacing null/empty with default values must be done inside Power Query Editor. Power BI converts all steps done inside Power Query Editor into a M Query and executes at data loading time. Therefore, although data loading consumes little time, it provides cleaned and well organized dataset for you.

Must perform operations in Power Query Editor:

· Remove all unwanted columns from source table (Flags, GUIDs, Time Stamps, etc.). If you are not sure about whether it’s required to business user or not, try to clarify from user and if that is not possible remove when loading.

· Rename field names into readable format.

· Rename table names into business-friendly readable format.

· Set appropriate data types for fields.

· Create calculated columns here as much as possible rather than using DAX to calculate columns inside model.

· Replace Null, Empty values with meaningful default values. (Example: N/A, Not Applicable)

. Create “Groups” to organize queries in order to  increase clarity of the Query panel.

. Disable loading of all intermediate tables and queries.

That’s it for the day. In next post I will discuss about best practice in naming convention in Power BI. Thanks for reading and cheers!

10 Cool Features in Power BI Most People Don’t Use

Power BI is being used by many people around the world and different people have different usage patterns. I have been using Power BI for a couple of years and I have seen how other people use Power BI within their organisations. With the help of that experience, I’m going to compile my top 10 cool features in Power BI which aren’t used by most of the people.

1) Preview Features

Power BI is one of the most versatile products in the Analytics world. Power BI team provide monthly updates to the product with each update having lots of cool features. However, most of these features comes as a preview feature initially and then it becomes generally available after couple of months. You can try all these new cool features if you go to the “Preview features” option.

How to go there?

Click “Files-> Options and Settings->Options-> Preview features”


2) Format painter

This is a very cool and useful feature I use every day.  Idea of “Format painter” is to copy all the UI changes you have done to a visual and apply same changes to another visual. This means you don’t need to change font settings, background colour, data label customisation, and all other UI related changes repeatedly to all the visual. Create one visual with all UI customisations and click “Format painter” button while having that visual selected. Then click on the other visual on which you want to apply same UI changes.

How to go there?

Click on “Home”-> “Format painter”. First you need to click on the visual which you want to copy all UI styles. If not the button will be in disable mode.


3) Phone layout

Well, some people use this and some don’t. Within a mobile,  Power BI reports can be viewed inside it’s native mobile app. Power BI gives you flexibility to design  a separate view for a report page when it viewed in landscape mode. As you know landscape mode does not have much visual space and hence you might need to ignore all the filers and big visuals  such as tables and matrixes. Keep it simple and focus on visuals such as cards and small bar column, bar charts when designing phone layout.


How to go there?

Click on “View”->”phone layout”


4) Gridlines , Snap to grid and Lock objects

When arranging visuals inside a page, alignments play a vital role and that’s where “Gridline“  options come to play. I use this feature always as it helps me to show how nicely aligned my visuals are within the page. Apart from that, you can select “Snap to grid” option so that it automatically align to the nearest grid line. Additionally, you can select “Lock objects” so that you won’t accidently move visuals that are already place nicely.

How to go there?

Click on “View”-> tick “Gridlines”


5) Table Layouts

When creating models, I have seen people keep all the tables inside default table layout view. This is acceptable only if you have couple of tables. However, when your model have 10 or mode tables, it become really difficult to view  and understand all the relationship. Create different table layouts for different star schemas. That way it is easy to modify the model and easy to understand as well.

Check this cool gif I got from Power BI blog which explains why table layout view is so cool.

Modeling View

How to go there?

Click ”Model View”->  “Plus” icon to add more views.


6) Group Visuals

This again useful as you want to arrange visuals within your page. Let’s assume that you have nicely arranged set of filters and visuals within the page and suddenly get a change request to add another visual to the page. Moving all the visuals around is really a hassle. For that, you can group selected set of visuals and then move the copy/move the group around. That way you don’t have to change spaces between visuals and alignments of visuals over and over again.


How to go there?

Select the set of visuals you want to group and right click. Then Click “Group” and again “Group” in sub menu.

7) Buttons

Did you know that you can add various buttons inside a report page?  Add buttons to give a nice user experience to your business users. For an example, rather than they have to click on each page name within web browser, you can add Arrow buttons to let them move between report pages. That is not the only usage of buttons. But that can be your starting point.

How to do that?

Click “Insert”-> “Buttons”-> Select what kind of button you want to add


8) Custom Visuals

Well, again, this is used by some people. but most of the people still don’t use custom visuals. Trust me, there are lots of cool custom visuals developed by various individuals as well as companies. Some of these custom visuals are free to use and some need licensing. Go to custom visual market place and have a look on what kind of visuals available. Sometimes you will find fascinating visuals like this. How about having fish swimming in your dashboard?


How to go to there ?

Go to visual section. Click on 3 dots as highlighted in below image.  Then click on “Import from App Store”. That will take you to custom visual marketplace and then you can select which visual you want to add.


Once that is added, it will be available just under all the other standard Power BI visuals. Check highlighted area below.


9) Power BI Templates

Do you want to share your Power BI report with someone else, but don’t want to share it with data? well, save it as a template and share it with your collages. Sharing reports with data can be a bad idea due to multiple reasons. First, the size of the file. If the report is created using Import modes which means it can be from couple from megabyte to hundreds of megabytes.  And you know what, you can’t email a file with 100s of MB in size. what about data security?  What you can do is rather that sending the report, save it as a template and share across. Then your collages can enter their data source connection information and populate the report. Your report, Their data.

How to go there?

Click “File”> “Export”-> “Power BI template”


10) Power BI Ideas and Voting

Last but one of the most import and coolest thing about Power BI. “Ideas”. Do you know that Power BI team enhance Power BI based on your needs and feedbacks?  Well they are. Lots of new features added to Power BI came as ideas from people like you and me. If you see that something is missing in Power BI, which is valuable for you as well as other. go to “” and check whether the same idea is already there. If it’s there, you can vote for that idea. Higher the votes, higher the possibility it gets added to Power BI. If the idea is not there , add it as an idea and people will vote for it. It’s win win for all of Microsoft as well as us. 

How to go there?


Thank you very much for reading this blog and cheers!!