Data Manipulation Using Power BI

 POWER BI : AN OVERVIEW

Power BI Desktop is a free desktop application that allows you to collect, manipulate, and visualize data. Both Power BI Desktop and Power BI Service function in tandem. You can build reports and dashboards in Power BI Desktop and then share them with others via the Power BI Service.

HOW TO LAUNCH POWER BI

The Getting Started dialogue box appears when you run Power BI Desktop, and it contains valuable connections to forums, blogs, and introductory videos. For the time being, close this dialogue box, but leave the option to Show this screen on startup selected so that you can investigate it later.

EXPLORING THE REPORT ENVIRONMENT

1. Ribbon - Displays common tasks that are associated with reports and visualizations.
2. Report view, or canvas - Where visualizations are created and arranged. You can switch between Report, Data, and Model views by selecting the icons in the left column.
3. Pages tab - Located along the bottom of the page, this area is where you would select or add a report page.
4. Visualizations pane - Where you can change visualizations, customize colors or axes, apply filters, drag fields, and more.
5. Fields pane - Where query elements and filters can be dragged onto the Report view or dragged to the Filters area of the Visualizations pane.

CREATING A VISUAL

HOW TO PUBLISH A REPORT

You're ready to publish to the Power BI service after building a report with a few visualizations. Select Publish from the Power BI Desktop's Home ribbon. Power BI will urge you to sign in. The following dialogue box will show once you've logged in and the publishing procedure is complete. You may click the Success! link below to go to the Power BI service, where you can view the report you just produced.

PINNING TO DASHBOARD

The next step is to pin the visual we created to a dashboard.

CONNECT TO DATA SOURCES

Power BI Desktop can connect to a variety of data sources, including local databases, spreadsheets, and cloud-based data. When you collect data, it's not always as well-structured or tidy as you'd want. You can alter data to structure it, which means splitting and renaming columns, changing data types, and creating links between columns.

Many types of data sources may be connected to Power BI Desktop, including on-premises databases, Microsoft Excel workbooks, and cloud services. There are roughly 60 Power BI-specific connections to cloud services like GitHub and Marketo currently available. XML, CSV, text, and ODBC can also be used to connect to generic sources. Even from a website URL, Power BI can extract tabular data.

GETTING DATA FROM EXCEL

You've most likely used Microsoft Excel to make or read reports, as well as to construct pie charts and other graphics. The method of importing Excel data into Power BI is simple. This section teaches how to load an Excel workbook file containing a simple table into Power BI from a local disc. Make sure each column in Excel has a decent name; this will make it simpler for you to discover the data you need when building Power BI reports.

IMPORTING FROM A LOCAL DRIVE

CREATING REPORTS

After the data from your worksheet is imported, Power BI creates a dataset, which appears under Datasets.

You may now start building reports and dashboards to explore your data. Select the Explore option from the Open menu icon next to the dataset. A second report canvas emerges, this time blank. Your tables and columns are located on the right-hand side, under Fields. Choose the fields for which a new visualization should be created on the canvas.

Under Visualizations, you may modify the kind of visualization as well as add filters and other characteristics.

You may also import data into Power BI if you utilize any of Excel's advanced BI tools, such as Power Query, Power Pivot, or Power View.

TRANSFORM DATA USING POWER QUERY EDITOR TOOL

Your data may contain additional data or be in the incorrect format at times. The Power Query Editor tool in Power BI Desktop can help you shape and manipulate data so it's ready for your models and visualisations.

To begin, open Power Query Editor by selecting Transform data from the Navigator pane. You can also use the Transform data button on the Home ribbon to open Power Query Editor directly from Power BI Desktop.

Right-clicking a column in the middle pane brings up the transformation options. Removing a column from the table, replicating the column under a new name, and altering values are just a few of the changes possible. You may also break text columns into multiples using standard delimiters from this menu.

Additional tools on the Power Query Editor ribbon can help you modify the data type of columns, add scientific notation, and extract items from dates.

As you apply transformations, each step appears in the Applied Steps list on the Query Settings pane. You can use this list to undo or review specific changes, or even change the name of a step. To save your transformations, select Close & Apply on the Home tab.

CLEANING OF THE DATA

While Power BI can import data from practically any source, it works best with columnar data for visualisation and modelling. In certain cases, such as with Excel files, your data will not be formatted in basic columns.

Power Query Editor includes tools for easily converting multi-column tables into usable datasets.

By using Transpose in Power Query Editor, you can swap rows into columns to better format the data.

Data may need to be formatted in order for Power BI to correctly categorize and identify it. You'll purify data into a dataset that you can utilize in Power BI with a few modifications. Promoting rows to headers, utilizing Fill to replace null data, and Unpivot Columns are all examples of strong transformations.

You may play around with transformations in Power BI to see which ones will turn your data into the most useful columnar format. Remember that Power Query Editor's Applied Steps section keeps track of all your operations. If a transformation does not operate as you expected, select the X next to the step and undo it.

You may start creating impressive graphics with Power BI when you've cleansed your data into a workable state.