Power BI: Transforming Data into Actionable Insights

Power BI is a business analytics service provided by Microsoft. It offers tools for aggregating, analyzing, visualizing, and sharing data. Power BI allows users to connect to a wide range of data sources, transform and clean the data, and then create interactive dashboards and reports that can be shared across an organization or embedded in an app or website.

The platform includes several components, such as:

1. Power Query: Used for data connection and transformation, allowing users to connect to various data sources and clean or shape the data.
2. Power Pivot: A data modeling tool that helps in creating data models, defining relationships, and performing complex calculations using DAX (Data Analysis Expressions).
3. Power View: A tool for creating interactive visual reports and dashboards.
4. Power BI Service: A cloud-based service where users can publish and share their reports and dashboards. It allows collaboration and real-time data analysis across an organization.

Power BI is widely used for business intelligence and data visualization, helping organizations make informed decisions based on data-driven insights.

Import Data

• Power BI can load and import data from more than 100 tools such as Excel, CSV, database, SQL, etc.
• In Home tab click Get Data then select the Tool from where you are importing the datasets.
• You can also paste from clipboard, In Home tab select Enter data, it will open a blank window of excel table and you can paste the copied data using control + V, give name to the table and then load it.

Charts in Power BI

Charts are available in Visualization pane

• Column chart
• Stacked column chart
• Pie chart
• Donut chart
• Funnel chart
• Ribbon chart

Include and Exclude Functions

• This function is used to Include and Exclude specific data instances.
• in column chart select using control click multiple different region and right click to select include - it will show the difference between the selected two.
• exclude works the same instead of showing them it removes them from the original chart.

View Data and Export

• view data can help you view data instances/rows of any particular region or values.
• select any region and right click on it then select “show data point as table”.
• you can also export this data by using more option and selecting export data in csv extension.
• we can allow or don’t allow the end user to view the data instances from setting of Power BI - File Menu > Options and Settings > Options > Current file > Export data > allow/don’t allow.

Maps in Power BI

• you need geographical data to create geographical maps in Power BI.
• select map chart icon from visualizations pane.
• some times the map won’t work because of poor data quality.
• the column names should be state, city, country, pin code, postal code.
• there is also a Filled map option same as map.
• another one is Map with Pie chart.

Table

• To create a table, go to visualization pane and select table, then drag and drop data columns from fields pane to value option in visualization pane.
• you can format the table as you want in format tab available in visualization pane.
• you can also add conditional formatting to the numerical values columns such as adding dark background color to the highest value and light background color to lowest value.
• you can add aggregation of values in table, using visualization pane and in value option select the average or whatever you want.

Matrix

• you can create a matrix from visualization pane, its nothing but 2 dimensional table, that has multiple rows and columns of data.
• you can also conditional formatting same as table
• you can add hierarchies in matrix rows
• you can change the data type of columns from decimal number to whole number, select the column you want to change from Fields pane then a new Tab in the ribbon will be visible as column tool, click on it go to datatype and change it.
• you can also change the way you see a values in column such as 10000 to 10k, go to field formatting in visualization pane and change the display unit option as you want.

Graphs

• Line chart
• Drill Down in Line chart
• Area chart
• Line vs Column chart
• Scatter plot
• Waterfall chart
• Treemap
• Gauge chart

Cards

• you can drag and drop cards from visualization pane, after adding numerical column field of cards it will show the total of the column.
• it will change value from another graph for categorical data you can also add filter to it.

Filters

• Filter on visual, you can add filters on a chart or graph by using this option.
• Filter on this page, will update all the chart and graphs on the current page with the filter.
• Filter on all pages, will add filter to all the pages.
• Drill through - to use drill through you have to add a column in drill through field present in visualization pane, let’s say you added column region now, it will show values such as central, west, east, etc. now jump to another page where there is a chart with region column in it and right click on any sub category of region column such as central, it will show the option of drill through to previous page name where you defined the drill through, after clicking it you will jumped there automatically and all the results and graphs will show about only the selected value such as central. in this way hierarchy is created between pages.

Slicer

• Slicer works like the filter to but you have to insert it from the visualization pane and add a categorical column in it to view the data based on that categorical value, you can also add sub category to a slicer.
• it is user friendly and interactive.
• you can format it for selection control to multi select values.
• you can add date column in slicer too, for viewing data based on selected date.

Advanced Charts

• There are more than 255+ types of chart in Power BI, including created by third parties, you can’t access them directly you need to have organizational email to access and use those new charts.
• using animation and third party chart will reduce the performance of the workbook.
• To insert visuals go to insert tab > more visuals > App store.
• to insert go visualization pane > more option … > get visuals > app store.

Animated Bar Chart Race

• import animated bar chart from get visual windows, click on it to implement it.
• add values same as a bar chart, and then add a date column in period option to view data periodically by date(years, months, days).
• you can format the speed of the animation on the chart from the visualization pane > visualization settings option.

Drill Down Donut Chart

• you can import advanced donut visual from store
• add categorical values to category option and numerical column to value option it will create a simple donut chart
• to add drill down functionality, add sub categories to category option, it should be hierarchical and it will create a drill down donut chart where you can go deeper and deeper in hierarchy.

Drill Down Column Chart

• import drill down column chart
• add date | categorical columns to category option and numerical column to value option it will create a column chart | bar chart with drill down option to months and days.
• below are the example of drill down column chart relative to years and days on x axis.

Word Cloud

• add word cloud from store.
• add categorical data in category option and (optional) add numerical column in variable column, it will create a simple word cloud | word map
• you can format it as you want

Sankey

• import Sankey chart.
• it is used to show relation between two variables, also known as source and destination.
• you can add values based on relation in weight option.

Infographic

• import Infographic designer.
• it same as column chart but more customizable, you can change the shape of the bar to icons available or import your own logo.

Play Axis

• import play axis (dynamic slicer).
• it doesn’t work independently, it needs other charts to visualize data.

Scroller

• import Scroller.
• add values in category and measure absolute options
• it will scroller through the chart
• you can format it as you want
• and if you want to add an indicator just move the value from measure absolute to measure deviation

Sunburst

• import sunburst
• you can add multiple categorical data in category option and
• numerical data in value option

Histogram

• import Histogram chart
• it shows the distribution of values in column
• you have to format it to show total number of values available in your
• data, go to format > general > bins.

Insert Objects

• Insert Images - go to insert tab > elements > image > select the image you want to insert. format it as you want.
• Insert Text Box - same as images, insert a text box, write text in it, format it.
• Insert Shapes - same steps, select shapes you want, format it.
• Insert Buttons - same as shapes, but have some functionality | action when clicked.

Action

• Tool Tip - there is a tool tip option available in format pane, it comes in action when the cursor is on the define object it hover the text of tool tip.
• Web URL - you can add an URL to open if clicked a button | image, just like hyperlink and <a> tag in html. go to format pane > turn on action > change type to Web URL > In web URL option enter the URL to be accessed.
• Page Navigation - create a button > open action option in format pane > select type option as page navigation > select destination option as the page name.
• Bookmark - bookmark is like a checkpoint in games, where you can specify and add bookmarks according to your need and use those bookmarks in action option of buttons to visit the bookmark.

1. to add a bookmark > make sure you’re in the right state | condition you want for bookmark > view tab > click bookmarks in show panes > a bookmark pane will be visible > click add to create bookmark > and rename the bookmark from more option.
2. if you want to hide some charts and graphs before creating bookmark > go to view tab > click selection in show panes > all the charts, graphs, buttons, images, etc. will be present there. > click on the eye symbol to hide | show the chart > and add bookmark.
3. edit | click button > turn on action > select type option as bookmark > select bookmark option as bookmark name.

• Drill through - it is like the drill through filter you have to add categorical value in drill through option in visualization pane on target page > then go to source page and create a button > turn on action > select type option as drill through > select drill through option as target page name, to view it in action select a category from a chart to activate the drill through button and press the button, you will jumped to target page with customized data.

Power BI Service

• Collaboration and Sharing: Share dashboards and reports with colleagues and stakeholders within and outside the organization. Embed Power BI reports in other applications using the Power BI REST API.
• Real-Time Data and Alerts: Set up real-time data streaming to update dashboards as new data becomes available. Configure data alerts to notify users when specific conditions are met.
• Mobile Access: Access Power BI reports and dashboards on mobile devices using the Power BI mobile app. View and interact with reports on the go.
• Steps to follow
• Create a Report in Power BI Desktop.
• Make sure you’re sign in Microsoft account with organizational email id, or create an account on Power BI Service.
• Publish Report to Power BI service account.
• Export (ppt, pdf, pbix) report and download offline, share
• comment, share and subscribe to a report - subscribe means automating the sharing process with your colleagues on periodically basis.

Dashboards

• you can’t create a dashboard in desktop, it only works in Power BI Service online.
• To create a dashboard > go to power bi service > My workspace > Dashboards > Create.
• to add charts in dashboards, open a report and hover on the chart, click pin option to pin it on the dashboard, choose which dashboard to pin on.
• unlike reports in power bi the dashboards aren’t interactive, to overcome this problem you have to pin the whole report with pin as live page option on top of report.

Automatic Refresh & Data Gateway

• you can refresh datasets manually from the my workspace section. if your data gateway configuration is met with the condition.
• to configure the gateway, go to my workspace > datasets > dataset name | more > gateway connection > install now. most of the time the admin will do this.
• you can scheduled refresh for daily, go to my workspace > datasets > dataset name | more > Scheduled refresh > turn on > frequency > daily > at time.
• you can add 8 different times to set for schedule refresh.

Power Query

• Power Query is a editor and transformation tool for data which is common in Excel and Power BI.
• to open the Power Query in Excel and Power BI click Transform when importing data, it will open power query editor.
• you have to select the table and sheets you want to transform in power query.
• after selecting the table, click transform data, it will open power query in power bi.
• there is no undo in power query.
• all the steps you took in power query editor will stored in applied steps pane, you can always delete and modify the steps.
• in the applied steps you can customize a step using the small setting icon on the step.
• After transforming data you can close it from Home tab > Close and Load.
• this whole process is called ETL - Extract, Transform and Load in Excel.
• if anything changes in source data the target will be automatically changed, you just have to right the target data and right click.
• If you want to change and transform the existing data columns then use Transform tab and if you want to add new columns from the existing columns use Add columns tab.

Text Functions in Power Query

• Before applying any functions, you have to select the columns which you want to transform, it can be done clicking on the header of the column.
• Merge - select multiple columns you want to merge in one > go to Transform tab, in text columns > select Merge columns > choose Separator such as “space, comma, semicolon, etc.” > give the new merged column a name > click ok, multiple columns will be merged in to one column and all the steps will be recorded in Applied Steps Pane. make sure to click close and apply after, you’re done editing.
• Split and Trim - select the column which values you want to split > go to Transform tab > in text columns > Split column > select delimiter if you want to split it by “, . -” or other characters > enter your delimiter > at split option choose “each occurrence of the delimiter” > click ok, make sure to rename the column. for trim - go to Format option > Trim. to remove the unwanted spaces.
• Upper, Lower and Proper - select columns which you want to format > same steps as trim > go to Format Option > choose lower, upper, capitalize.
• Prefix, Suffix - select the column you want to transform > follow same steps as trim > select add suffix | add prefix > enter the value of suffix > click ok.
• Left, Right and Mid - select the column you want to get the value from > go to transform tab > select extract > choose first characters > add count 3, — you can even select the range as an option for extract > enter starting index > enter to number of character to extract, — you can also extract text before, between and after delimiter > select text before the delimiter option > enter delimiter.

Date and Time Functions

• when you load the dates column in power query it will be load it and apply change datatype step as date datatype.
• Extract - to extract year, month, quarter, week, day from a date column > select the column > go to add column tab > select date > select year | month | etc. > select year.
• Difference Between Dates - to calculate the difference between dates > select both columns > go to add column tab > select date > select subtract days | Earliest date | latest date.
• Name of the Day and Month - to view the name of the day and month from a date column > select the date column > same steps as extract > select day | month > select name of the day | month, with these steps you can find out day of the week | year, also week of the month | year.
• Extracting date | time from date and time - same steps as extract > select date only. | go to time > select time only.
• Calculating Age - to view the difference between the given date and current date > select the column > go to add column > select date > select age, it will give the difference in days and time > select the column aka duration datatype > select duration > total years, to convert it to years in decimal format change its datatype to whole number (int).

Number Functions

• Multiply - to multiply two numerical columns > select both columns > go to add column tab > select Standard > select Multiply, similar process for subtract, add, divide, etc.
• Percentage, Modulo, Percent of - similar steps as multiply, but you have to define the percentage value.
• Round Up, Down, Round up to - to round the decimal value to next or pervious whole value we use round up and round down, and for rounding up to 2 decimals we use round up to > similar as multiply > go to rounding > select round up.
• is Even, is Odd, Sign - to check if the value is even or odd | to view if the number is negative or positive use sign > similar step as round up > go to information > select is even.

Multiple Files Loading | Merging Files and Tables

Append multiple CSV files from a folder

• make sure you have all the csv files you needed in a single folder
• open Power BI
• select blank and get data
• select more
• select folder and connect
• enter folder directory path or browse to the folder and click on ok
• select combine
• select combine and Transform data | combine and load

Append multiple Excel tables | sheets from

• single excel file
• select get data
• select excel
• select the excel file and open
• in the preview window select all the sheets from the excel file
• select transform data
• in home tab > combine section > select append queries > select append queries as new > select three or more tables > select all the sheets present there.

Append Excel tables | sheets with different number of columns

• conditions - columns name should be same positions and numbers can or can’t be same.
• select excel file from get data and select the file and open it
• in preview window select all tables and transform data
• same as append queries as new open it.
• all the empty values will be shown as null
• after transforming data if you don’t want to see the other individual queries select them in power query, right click on it and disable the option of enable load.

Append multiple Excel files from a folder

• make sure you have all the excel files you needed in a single folder
• open Power BI
• select blank and get data
• select more
• select folder and connect
• enter folder directory path or browse to the folder and click on ok
• select transform data, open power query, go to add column select custom column, in the custom column window’s custom column formula enter - Excel.Workbook([Content]) ,click ok
• it will create a custom column with table value, select drop down arrow in custom column header, click ok
• go to custom kind column, filter out the sheets so that only tables are visible.
• select name, custom_item, custom_data columns and remove others from the right click option.
• select drop down arrow in custom_data header and click ok.

Append different data source files in power query

• you can load different files one by one in power BI, but to append it you have to transform it in power query.
• load multiple files, go to transform
• use append query as new

Merge Table | Sheets in Power Query

• use get data to preview the excel file, and select the tables you want to merge, and click transform and load.
• after opening power query in home tab, go to combine section and select merge queries > merge queries as new.
• in merge window select both the tables you want to merge and select the common column from both the table and click ok.
• click the dropdown arrow in merged column header, and select the column names from the right table to visualize and click ok.

Merge Data from multiple Excel files | Workbooks

• Load multiple files in power bi one by one and use the transform data option to open power query.
• use the same step as merge queries as new.

Merge data from different data sources

• load data in to power bi from different data sources one by one.
• use same step as merge queries as new.

Merge data having multiple matching columns or multiple criteria

• get data in to power query and use merge queries as new, select both the tables
• select multiple columns from the first table and same columns in the second table with the same order of selecting.

Column from Example (flash fill) | Conditional Column (conditions)

Column from Example

• get data and transform it to open power query
• select a column > go to add column tab > select column from example > select From selection.
• enter what you want to split from the selected column such as “California - Ca”, press enter and click ok.
• if the power queries preview is not right, go to the next row and enter its value such as “California - Ca, Denmark - De”, keep it training until its understand.
• same steps for merging columns, date columns, alphanumeric columns.

Conditional Column

• get data and transform it to open power query.
• select the column for condition > go to add column > select conditional column.
• in the conditional column window, write the column name then after if write condition column operator equals| is greater than | is lower than | etc. value enter value "400" then output write expected output "high profit" , enter multiple conditions using “add clause” button, else enter else value "average profit".
• you can select multiple columns in column name value for each condition.
• you can select two columns to compare the value of first with second column by changing the value data type to select a column in condition window.
• you can use this for date and time data type by comparing it to date value, it can be accessed by small calendar icon after the value option.

Important Topics

Group By

• go to transform tab > group by > enter new column name > operation > column.
• Transpose
• to change a table from horizontal (landscape) to vertical (portrait).
• remove the null values rows and columns if any > in home tab > remove rows > remove top rows > enter removal rows count.
• if you want to keep rows and delete other > in home tab > keep rows > keep top rows > enter rows count.
• go to Transform tab > transpose.
• use first row as header in transform tab.

Unpivot

• to unpivot like the above image, select a fixed column such as year in the image > then right click on its header > select unpivot other columns.

Replace Errors

• to replace errors > go to transform tab > select replace > replace errors > enter value such as 0.

Handling uncleaned data

• in home tab > use remove rows > remove blank rows.
• select all columns and rows using control + a > use remove rows > remove duplicate rows.
• there is also keep column and remove column.

Created by Md Tabish Shaikh.