If you have closed the Query Settings pane (the pane that has the APPLIED STEPS) in Power Query of Power BI and need to bring it back (reopen it), go to the View ribbon, and click the “Query Settings” icon.
The Query Settings pane which shows all the APPLIED STEPS (transformations) that have been applied to the data brought into the Power BI application will open at the right of the screen (where it normally resides).
Thanks for reading!
It is common to have the need to perform time-driven analysis on your data. For example, you may need to compare this month’s sales with the sales from the same period a year ago or you may need to calculate the number of days between two dates. Power BI provides a set of Time Intelligence functions that make it easy to perform these types of calculations. But to take advantage of the Time Intelligence functions in Power BI, you must have a date table in your Power BI model and to have that you will need to “mark” a table as a date table.
To mark a table as a date table, it must meet the following criteria:
Once these rules are met, you can then mark the table as the date table. To do this, you can either right-click on the table in the Data pane and select “Mark as date table” (as shown below):
Or with the table selected in the Data pane, from the Table Tools tab, click on the “Mark as date table” icon. This icon will be grayed out if there are no date columns in the table.
The “Mark as date table” dialog opens (which includes a warning), from which you can turn on the “Mark as date table” flag.
Turn on the flag, and then select / set the date column from the dropdown.
Power BI will then validate your data to make sure all the criteria is met for the chosen column. If you get an error, make the necessary changes and try again.
Thanks for reading!
When working with data, you may need to add an index column (or an ID column) to your data. For example, you may have a list of products (names and descriptions) but you do not have an ID for each product, and you need to assign one to each product, perhaps for data modeling purposes.
To add an index or ID column to each row, you will do this in the Power Query Editor. With your query (table) open, go to the “Add Column” tab where you will see the “Index Column” option.
You can click the menu item to use its default option – which will add an index column that starts at zero (0) – as shown below.
Or you may click the dropdown (to the right of the Index Column menu item) to choose from 3 available options (From 0, From 1, or Custom).
“From 0” will add an index column that starts at zero (0) as shown above (in the default example).
“From 1” will add an index column that starts at one (1), as shown below:
“Custom” will open a dialog for you to enter the “Starting index” value and the “Increment” between each value. In the example below, the starting index value is set to 1000, and the increment is set to 5.
This means that the first value in the index column will be 1000, the second value will be 1005, the third 1010, and so on. This is shown below.
Once your index column is added, you can rename it and move it as desired. For example, you may rename the column name to Product ID and move the column to the front of the table as shown below.
Thanks for reading. I hope you found this useful.
Enter Data
Whether you need to create a small lookup table or you need data in a small table to test an idea or feature, you may have the need to manually or directly enter data into a table in Power BI. To do this you will use the “Enter Data” feature available in the “Home” tab/ribbon in the Power Query Editor.
Click the “Enter Data” ribbon icon. It will open the “Create Table” window (dialog) – shown below.
Enter the desired data, including the column names for as many columns as you need, and the values for as many rows as you need. However, typically this feature is used for small table (few columns and few rows). It is best to have larger data tables in a database or file. In the example below, 3 columns and 5 rows of office location information were entered.
Also, name the table appropriately. The table is named “Location” in the example above.
Once you are done entering the data and naming your table, click OK. The table will be added as a query. You can now use this table/query just like any other query in Power Query.
Update Data
After you have created the new query, how do you update the data? Whether you need to make a correction or an update to existing data or add new data, select the query, then in the “APPLIED STEPS” pane to the right, click on the gear icon in the “Source” step.
This will reopen the “Create Table” window from where you can add, delete, and update any of the records.
Thanks for reading.
Power BI allows you to sort one column, such as a column displayed in a visual, by another, such as one that is not displayed. Here we will explain how this is done.
A very common case of this is with “Month Name”. You typically want to display the Month Name (Jan or January, …, Dec or December, etc.) and not the Month Number (1, 2, …, 12). And instead of sorting the month name alphabetically (Apr, Aug, Dec, …, Sep), typically you want to sort it chronologically (Jan, Feb, Mar, …, Dec).
In this made-for-example scenario, you have Sales by Month, and when you sort by Month, it is displayed as shown below – as expected in alphabetical month order.
But as mentioned before, we really want the months sorted in chronological order. To do this, we need to sort the Month Name by the Month Number.
Click the “Month” field (month name) in the Data pane, and then click the “Sort by column” icon dropdown, and select “Month No” (month number). This is telling Power BI to sort the month (Month) by the month number (Month No), instead of the month name (Month).
So, now your Sales by Month visual with the same sorting will look like the below – sorted by month chronologically.
Thanks for reading!
Often bringing data into Power Query, you may need to exclude (or later decide to include) some of the columns in the dataset from your Power BI model. Here we will go over the best way to do that – using the “Choose Columns” feature.
After bringing a file or table into Power BI, to manipulate it you can choose “Transform Data” in the Power Query Editor if you have not yet loaded it, or if you have already loaded it, then from the Table View select “Edit query” to open the table/query in the Power Query Editor.
In the Power Query Editor, from the Home ribbon, click the “Choose Columns” icon or click its drop down and select “Choose Columns”.
By default, all the columns are selected. Then, from the Select Columns dialog, you can select (to keep) and unselect (to remove) any columns you chose to.
This is most useful when you have a query (table) with a lot of columns, and you need to remove several of them, and especially if they are not all right beside each other (not contiguous). This option is more convenient than scrolling through a wide table and using “Ctrl + Select” to select all the columns you want to keep or remove.
In addition, if you later realize you need to select or deselect another column, you can just go to the corresponding step in the “APPLIED STEPS” pane, select the step’s gear icon to reopen the Choose Columns dialog, and then select or deselect column(s) as necessary. This is much better than adding another step for performing additional column deletions or removing a previous step to undo previous column deletions.
Thanks for reading!
Creating a Calculated Column or a “Calculated” Measure in Power BI will both result in a new field that you can use in reports. However, they are different in several ways.
A Calculated Column is evaluated at the row-level and returns/stores one value for each row. For example, if you have the two columns, Revenue and Expenses, you can create a Calculated Column for Profit using the following formula: Profit = Revenue – Expenses. Profit would be calculated for each row during data load and would add a new column to the dataset and be stored with the dataset, and therefore, increase the size of your Power BI model.
A Measure on the other hand, is evaluated across multiple rows. For example, you can sum your entire Revenue column to get Total Revenue. Your formula would be something like: Total Revenue = SUM(Revenue). In the case of measures, they are not new columns in your dataset, but are fields that can be added to visualizations that are calculated at query time when accessed. Therefore, Measures are not stored and do not increase your model size. Measures, such as Total Revenue, will adjust based on the filters applied to the data. For example, if you applied a filter of Year = 2021, then the Total Revenue would be the sum of Revenue for the year 2021, and this is why Measures must be calculated at query time, unlike Calculated Columns whose individual row values are not affected by filters.
Here’s a tabular summary of Calculated Columns vs Measures:
| Calculated Columns | Measures |
| Profit = Revenue – Expenses Calculated for each row Returns one value per row | Total Revenue = SUM(Revenue) Calculated in aggregate across multiple (possibly all) rows Returns a value based on some aggregate across many rows |
| Adds a new column; is pre-calculated and stored in memory along with the rest of the data; increases model size | Adds a new field; is not stored in memory but makes use of CPU to perform the calculation; does not increase model size |
| You can see the results of a Calculated Column as soon as you define it | You can see the results of a Measure only after it has been added to a visual and displayed |
| Calculated at data load | Calculated at query time, such as when a user opens a visualization that includes the field or when the user changes the filters applied to the data being displayed |
| Column names, including Calculated Columns, must be unique within a table. Different tables may have the same column name. | Measure names must be unique across the entire data model. Therefore, tables cannot contain Measures with the same name. |
Generally, since Calculated Columns increase the model size and can degrade performance, unless you intend to use the values from your Calculated Columns as categories or filters in your visualizations, it is better to create Measures.
Thanks for reading. I hope you found this article useful.
Good luck on your analytics journey!
One of the first things you may notice when creating new reports in Power BI, especially if converting existing reports from other BI tools to Power BI, is that the canvas (page) size is relatively small. In many cases it is too small to hold all the visualizations, slicers, text and headers you want to include. This is by design I think, where the intent is to keep the number of visualizations and information on a report page to a focused set. However, you may find that the default settings are a bit too small. You can customize the canvas size as follows:
In the Report view, make sure that no visualizations are selected (click on a blank area on the report page if necessary). Go to the Visualizations pane.
Click the Format icon. Then expand the Canvas settings section. There you can choose Custom from the Type dropdown, and set your desired custom Height and Width for the canvas area.
Note: If you have not upgraded your Power BI Desktop in the last few months, the Visualizations pane and Format icon may look a little different.
Thanks for reading. I hope you found this useful.
Power BI allows us to connect to many different data sources – from relational databases, NoSQL databases, files, and more – to source data for consumption in Power BI. From the data sourced, you can create additional data (new calculated columns, metrics, transformed data, etc.), build data models, and create reports and dashboards.
There are a few storage modes related to how the data is retrieved, stored, and processed in Power BI. The storage modes are Import, DirectQuery, Live Connection, and Dual. The storage mode is set at the table level for each table in the Power BI data model. I will now describe these modes.
Import
With the Import storage mode, Power BI imports and caches the data from the sources. Once the data import is complete, the data in Power BI will remain the same until is refreshed by the Power BI refresh process for that dataset.
This storage mode allows for the usage of the most Power BI features for data modeling and analysis. For example, Import mode is required for using two of the popular Power BI features, Quick Insights and Q&A. Also, this mode is almost always the best for performance. However, it’s not necessarily the best option in all scenarios. Since the data is imported, the file size can get large and can sometimes take a considerable amount of time to load. But generally, for relatively static, low volume data, it is the preferred choice.
Queries submitted to an imported dataset will return data from the cached data only.
DirectQuery
With the DirectQuery storage mode, no data is cached in Power BI, but the metadata of the source tables, columns, data types, and relationships is cached. Instead, the data is directly queries on the source database when needed by a Power BI query, such as when a user runs a Power BI report that uses the data.
For Since the data is not imported, if all the tables in the data model use DirectQuery, the Power BI file size will be very small compared to a model with imported data.
Live Connection
The Live Connection storage mode is a special case of the DirectQuery mode. It is only available when sourcing from Power BI Service datasets or Analysis Services data models. There are limitations when using this mode. Data modeling is limited to creating measures, and therefore, you cannot apply transformations to the data, and you cannot define relationships within the data. And you can only have one data source in your data model.
Dual
With the Dual storage mode, a table may use Import mode or DirectQuery mode, depending on the mode of the other tables included in the query. For example, you may have a scenario in which you have a Date table that is connected to one transaction table that needs to reflect the data in the source, and is therefore set to DirectQuery mode, and also connected to another transaction table that only has less than 100,000 rows and is set to Import storage mode. By setting the Date table to Dual storage mode, Power BI will use DirectQuery when the query involves the date table and the first transaction table, while using Import mode when the query involves the date table and the second transaction table.
The below table summarizes the Power BI data storage modes:
| Import | DirectQuery | Live Connection | Dual |
| -Data is imported and cached in Power BI -Preferred for static, relatively small datasets -All Power BI functionality is available – including DAX, Calculated tables, Q&A and Quick Insights -Can connect to Analysis Services but Live Connection is preferred -Can have unlimited data sources -Typically provides the best performance | -Data is queried on the source when needed -Use for large datasets and when data changes in source need to be updated immediately -Features such as Q&A, Quick Insights, Calculated Tables, and many DAX queries are not supported -Limited data transformation functionality -Parent-child functionality not supported -For relational databases -Not supported for Analysis Services -Performance greatly dependent on the source data source | -A special case of DirectQuery -Used for connecting to multi-dimensional data sources, such as Analysis Services -Can be used only with Power BI datasets and Analysis Services -Can have only one data source -No data transformation available -Q&A and Quick Insights not available -Can create measures | -A combination of Import and DirectQuery -Power BI will choose the appropriate option based on the storage mode of the tables involved in the query -Can improve performance |
Note: the content in this post is relevant for the PL-300 Analyzing Data with Microsoft Power BI certification exam.
Thanks for reading! I hope you found this information useful.
Good luck on your analytics journey!
Power BI has 4 roles. Those roles, in order of increasing access/capabilities, are Viewer, Contributor, Member, and Admin. Before granting roles to users in your environment, it’s best to have a solid understanding of what each role has access to and is capable of doing.
The table below provides a list of capabilities of each role. As you will see, each roles “absorbs” or “inherits” the capabilities of all the roles below it in the hierarchy – for example, the Contributor can do everything the Viewer can do plus more, and the Member can do everything the Contributor can do plus more.
The Power BI Workspace roles
| Viewer | Contributor | Member | Admin |
| View dashboards, reports, and workbooks in the workspace | Everything that the Viewer can do | Everything that the Contributor can do | Everything that the Member can do |
| Read data from dataflows in the workspace | Add, edit, delete content workspaces | Add other users as members, contributors, or viewers to the workspace | Update and delete the workspace |
| Row-level security applies to viewers | Schedule refreshes and use the on-premises gateway within workspaces | Publish and update the workspace app | Add and remove other users of any role from the workspace |
| Feature dashboards and reports from workspaces | Share and allow others to reshape items from the workspace | ||
| Have access to the lineage view | Feature the workspace app | ||
| Have full access to all datasets within a workspace |
A few things to keep in mind regarding roles:
This article was intended to be an easy read; more detailed information regarding Power BI roles can be found here on the Microsoft site.
Thanks for reading!
The Data Community 