From basic Excel User to Power BI Expert in a Day
Have you ever thought of building interactive automated graphs and visuals while working with Excel? Has it ever occurred to you, how beneficial it would be if you did not have to transform and edit a dataset each time you had a new set of data available? Or, have the Excel limitations of working with data caused you problems? Then this article may help you to learn and adapt an efficient way of working with data. The time and effort saved using Power BI as a data transformation and visualisation is one of the many advantages of Power BI that we will see in this article.
Many of the same data management tools and mechanisms exist in both Microsoft Excel and Power BI. This means that Excel users are in a unique position to move to data modelling and visualisation in Power BI! Using methods that will feel familiar, you can learn to use Power BI to make data-driven business decisions based on big data. This article will help you build foundational Power BI knowledge and skills, including importing data from Excel and other sources into Power BI; understanding the Power BI environment and its three views; building beginner to intermediate skills for navigating the Power BI product; exploring influential relationships within data sets; designing Power BI visualisations and reports; creating effective dashboards for sharing, presenting, and collaborating with colleagues in Power BI Service.
Power BI (Business Intelligence) is an industry-leading Microsoft BI tool. It is used to:
1.???? Analyse
2.???? Model
3.???? Visualise
4.???? Share Data
How does Power BI work?
??????????? The ultimate purpose of Power BI is to put the power of data-driven decision-making into the hands of the businesspeople in companies of any size, anywhere. Power BI reports are designed to integrate seamlessly with other Microsoft Applications such as Teams, PowerPoint and SharePoint.
?
1. Overview of Power BI?
Power BI has two sub-parts, each having their own function:
?
Reports are created in?the Power BI desktop application?and then published to the Power BI service. The Power BI service is?where you can engage with dashboards,?workspaces, and apps.?
?
Steps for creating a Power BI Report:
?
1.???? In the Power BI desktop application?begin by importing data?or connecting to a data source.
?
?
2.???? Then click transform, and shape the data in the table view,?alternatively known as the data view.?
3.???? Then model the data by?editing relationships between tables.
?
4.???? When the data is ready,?start creating a report by adding?visualisations to the report canvas.
?
5.???? There are many visualisations to choose from,?and you can even download more from app source. Visualisations allow you to gain new insights into your data so that you can tell a specific story.
?
?
6.???? Once the report is ready, publish it to the Power BI service. The Power BI service can be accessed from the browser on the Microsoft 365 home page or from any web app. Simply click the app launcher icon and select Power BI. The Power BI service is where you can find and utilize workspaces, dashboards, and apps.
?
?
7.???? Dashboards allow you to add tiles for multiple reports so that you can see important metrics at a glance. Workspaces are containers for Power BI items. My workspace is private, whereas workspaces are shared.
8.???? Apps are collections of related dashboards, spreadsheets, data sets, and reports bundled together for easy distribution.
?
?
2. Import and Clean Excel Data in Power BI
?
??????????? Importing Data:????????????? ????
·????? The first step toward integrating Power BI and Excel is importing your Excel datasets into Power BI. When working inside of the Power BI desktop application, you can import data from a variety of different locations. ?Connect to a data source by using the Get Data feature on the home tab.
·????? The table view, also known as the data view, allows you to view and edit your data after it has been loaded into the application. This view can help you make informed decisions about your report by allowing you to review the data down to the row level and examine it from different angles. Since the table view shows data after it has been loaded, the data icon will not be visible if all data sources are based on direct query. Use the data pane to select or search for a table you want to view.
·????? The data grid displays all columns and rows from the table. Right-click a column for options such as sorting.
·????? To filter a column, select the filter drop-down for the corresponding column.
·????? From the Table tools tab, one can edit the table and manage relationships.
·????? In the calculations group, you can create new measures, columns, and tables with DAX expressions or formulas. Measures are calculations performed on data using formulas or expressions that summarize your data and can be used to create visualisations. In the formula bar, enter DAX expressions for measures and more. The new measures, tables, and calculated columns you create will appear in the data pane.
?
?
?
·????? From the Column tools tab, one can edit a column's name and how it is formatted.
·????? In the properties group, one can set the summarization and data category options for a particular column, which will impact how the data is displayed in all visualisations in the report.
·????? From the Sort by column drop-down, you can choose a column to define the sort order of another column in the report's visualisations.
·????? Use the table view to gain further insights into data and make well-informed decisions about the report.
·????? From the Home tab, one can select new data sources to connect to, navigate to the power query editor to transform the data and refresh the visuals in the report with the latest data from the original data source. In Power BI, one can import or connect to data from files, databases, the Power Platform, Azure online services, and more.
Cleaning and Transform Data:
·????? When importing data into Power BI, seemingly minor errors can mean the difference between accurate reports leading to success and flawed reports leading to failures even something as simple as random misspellings, can cause the app not to detect crucial relationships that could have significantly affected reports and the directions they lead in. This is why it's imperative to clean, shape, and transform data before moving on to the modelling and report visualisations phases of the Power BI process.
·????? Power Query is a fantastic tool for these purposes because it cannot only connect to and clean a wide variety of data types. It can also store reshaped data in many locations. Power Query Editor has an additional benefit of remembering data transformations from the original extraction of your data and applying the same transformation process whenever the data set is refreshed or updated. This is called a query. Power Query Editor is not truly changing the source data. Instead, it is simply changing how the data is displayed.
·????? To Access the Power Query Editor, select the home tab. In the Queries group, click the Transform data dropdown. Select transform data. The Power Query Editor is used to transform, edit, and select data before loading it into Power BI. In the Queries pane, one can select the query to edit.
·????? While working in the power query editor, it is visible that every transformation that is applied to the data is recorded in the applied steps list. The applied steps list functionality is valuable because it shows how the data was shaped and cleaned.
?
?
·????? From the Transform Tab option, one can execute basic math operations to a column. The standard feature allows to add, subtract, multiply, or divide all column values by a specified number, and more.
·????? The Transform Tab option also has the option to replace errors in a column. One can replace errors in a table by inputting the value to be shown instead. This doesn't fix the source of the error, but it will fix how the values are displayed.
?
领英推荐
3. Model and Visualise Data in Power BI
?
???????????Modelling Data in Power BI
·????? Data Modelling, Report View and Visualisations is the next step after Importing Transforming the data towards creating an impactful Power BI Report.
?
·????? The Model View displays all the tables, columns, and relationships in the model. In the left navigation pane, click the "Model Icon". Table cards are displayed, illustrating the relationship between each table in the model. Related tables are connected by relationship lines.
?
·????? Power BI can automatically detect relationships when first importing data. But sometimes relationships between tables need to be edited. Edit Relationships to determine how the data is displayed and interacts with each other.
·????? Relationship cardinality determines whether the relationship will be many-to-one, one-to-one, one-to-many, or many-to-many. Click the appropriate option.
·????? Once a relationship is created between two tables, one can begin working with them as if they are a single table.
?
Visualising Data in Power BI
·????? Visualisations are visual representations of your data. They include every kind of graph, chart, and tree-map. Furthermore, they are customizable, making these graphics one of the most impactful features Power BI has to offer, because they help to interpret and discover new insights about the data set.
·????? There are four main types of visualisations in the report view:
1.???? Chart visualisations (Bar Charts, pie Charts, Scatter Charts, etc.)
2.???? Texts visualisations (KPI Charts, Key influencers Chart, etc.)
3.???? Geospatial visualisations (Map visualisations)
4.???? Custom visualisations (Visualisations created by Query and Q&A)
·????? The report view is where one creates dynamic and informative report pages with visualisations. The report view is used to create informative and engaging reports that tell a story with the data.
?
·????? In the visualisations pin, one can select visualisations and customize them to tell a compelling story with data.
·????? Take some time to familiarize with the available visualisations. As each visualisation type is best suited for different contexts. Select or drag data fields from the data pane to populate the visualisations.
·????? Review the formatting options that are available to customize the visual selected. To add reference lines and focus on important trends or insights in the visual, click the analytics icon.
·????? From the filters pane, one can apply filters to specific visuals, the current report page or all pages within the report. Applying filters allows to focus on specific categories in the data, at the visual level or across many visualisations to get a full picture.
·????? From the view tab, one can change the look and feel of a report canvas. One can select a theme to update the colours and formatting of the visualisations in the report.
·????? One can also select the page view, report page options, and the pins that are visible while editing the report. Once it is finished creating a report, one can publish it to the power B I service by clicking Publish on the Home tab.?
·????? While editing the report, there are numerous formatting features that vary by visualisation type. Select the visualisation that is to be edited. In the visualisations pane, click the format visual icon. The formatting options available for the selected visual appear. Depending on the visualisation type, one can edit the colours of the legend, axes, rows, lines, data colours and labels, shapes, and more.
?
4. Work with different types of Visualisations
·????? There are different types of Visualisations available on Power BI. Some of them are:
?
1.???? Text Visualisations: These put greater emphasis on the plain texts in the data sets. They display data using a combination of text and numerical, or chart formats.
?
2.???? Card Visualisations: These visuals display data points one row at a time. There are two kinds, single number and multi row. A single number card is most likely to be used if there is one very important data point to be highlighted, such as total annual sales. A multi row card is used when there is more than one data point to highlight, such as annual sales by age demographics.
?
3.???? Table Visualisations: It's a grid of rows, columns, and headers, which contain an organized related data. They work best in instances where values grouped into various categories need to be compared quantitatively.
?
4.???? KPI Visualisations: A Key Performance Indicator or KPI is any measurable value that can tell how the data's actual performance measures up against the goals set for the business. The KPI chart visualisation is the visual representation of where data falls within the parameters of the goals. In order to create a KPI visualisation must have: a base measure value, a target measure value, and a goal also called a threshold.
?
5.???? Key Influencer Visualisations: Key influencers are factors that contribute to important data trends and oftentimes can be things like buyer demographics, geographical region, or the performances of competitive brands. The Key Influencers Chart analyses the data and ranks the main factors.
?
6.???? Chart Visualisations: These can be used to tell the story of your data.
(a)?? Pie charts are best used to represent data as parts of a whole.
(b)? Bar and column charts are best used to compare specific values across different categories.
(c)?? Scatter charts combines two values, the X axis and Y axis, into a single data point. They are best used to highlight intersections in data. Types include Scatter, bubble and dot plot chart.
(d)? Waterfall charts are best used to show how a value is affected by positive and negative changes and they show a progression from the initial value.
(e)?? Funnel charts or sequence charts. Best used to show how data flows between stages.
(f)?? Ribbon charts are best used to show highest to lowest values or rank.
?
·????? Analytics tools are used to extract and highlight data insights when viewing reports or presenting visualisations to an audience such as Filters, Slicers, and Data Drilling.
·????? In the Filters pane, one can apply filters at the visual, page, and report level to focus on specific pieces of data while creating a report.
·????? A slicer is a unique element in that it is both a filter and a visual. It is most often used when report builders or their audiences need a standalone chart, which can be used to filter other visuals on the page. It makes it easier for the audience to apply commonly used or important filters on the entire report page by adding slicers. “Chiclet Slicers” show filters using Images such as of logos or brands, making the filter more user-friendly.
·????? With the Drill Up and Drill Down features, one can switch between more general and granular views of the data.
·????? Focus Mode allows to expand a visual to see it in more detail while editing it.
·????? With power BI's Analyse feature, one can get quick insights into where a distribution is different in a visualisation.
?
5. Work with Dashboards in Power BI Online
?
·????? Up until this point, we have been working in the Power BI Desktop application,?focusing on data analysis, data modelling, and report building. Reports are created in the Power BI desktop application, and then published to Power BI Service, also called Power BI online.
·????? The Power BI Desktop application is where one cleans, transforms, shapes data, models data, and creates report visualisations. Power BI Service is where one views workspaces, creates dashboards, modifies report visualisations, and shares data with others.
·????? Power BI Service is a collection of five components workspace and my workspace, dashboards, datasets, apps, and reports.
·????? Workspaces are basically containers where one can go to view dashboards and reports. My workspace is a location only the publisher has access to. Consider it as a space to work in private. Workspaces are locations others with Power BI licenses have access to. Workspaces are to collaborate with colleagues, partners, and other associated persons with an investment in the reports.
·????? Dashboards are comprised of many visualisation tiles that are connected to different reports. Those reports and visualisations created in the Desktop application, this is one primary location they go to be shared with, commented on, and sourced by others. Dashboard is where the magic of collaboration happens. Dashboards are built to be efficient portals as well, since pinned visualisation tiles retain their links, allowing to click on the tile to be taken to the full report of origin.
·????? Once it is ready to share a report or dataset, one must first publish it to the Power BI service, which is sometimes known as Power BI online. From the Home tab, click "Publish" > click "Save" > In the selected destination box, locate and select the workspace to publish the dataset or report to > Click "Select". The report or dataset is now published to the Power BI service. Once publishing is complete, click the "Open in Power BI" link to be taken to the report in the desired Power BI site.
?·????? Dashboards in the Power BI service allows to capture the most important metrics across multiple datasets and reports onto a single page often called a canvas. They can be comprised of pinned, visualisations, and other elements called tiles.
·????? While reports allow to analyse a single dataset, creating a dashboard can help monitor the overall health of a business, product, campaign, and more. When one pins a tile to a dashboard, a connection between the dashboard and the report element is established, and it can be refreshed at any time to show the latest data.
·????? Once the reports are published, creating dashboards is a great way to monitor the most important information from multiple reports and datasets in one place.
·????? Focus mode is helpful when one is presenting a dashboard since it allows to expand visuals and view them in detail.
·????? While editing a dashboard, one can choose from several different types of tiles, including web content, images, text boxes, and videos. When editing of dashboard is finished, share it. For example, by sending a link of the dashboard through Microsoft Teams, PowerPoint or email.
?
In conclusion, the transformative journey from a basic Excel user to a proficient Power BI expert is not just a series of steps; it's an empowering experience. The seamless flow from importing and transforming data to modeling and visualizing it in the Power BI desktop app lays a solid foundation. Once your masterpiece is published in the Power BI Service feature, sharing becomes a breeze – a simple link connects you with colleagues, fostering collaborative insights.
?
The added feature of dashboard subscription elevates the experience further. Subscribers receive regular updates, tailoring their engagement with the data. The flexibility to choose update frequency and timing ensures that users stay in control of their information flow. The cherry on top is the personalized email updates, seamlessly aligning with selected preferences and settings.
?
In essence, what might seem like a technical process unfolds as an engaging journey. These step-by-step procedures not only guide users in building and publishing Power BI reports and dashboards but also serve as a gateway to making impactful, efficient, and timely decisions in both business and project landscapes. So, bid farewell to the limitations of basic Excel usage – embrace the realm of Power BI expertise, where data becomes a tool for informed and impactful decision-making.
?
?
?
?
?