How to prepare data for Power BI dashboard using Excel dataset

1. Dataset used

• The dataset we are going to use for this lesson is Microsoft Excel file named “Monetary Pattern.”
• You’ll be able to obtain the dataset by clicking on the picture under. Please guarantee to retailer the dataset in an acceptable location in your pc.

2. Importing knowledge

• Launch your Energy BI Desktop software.

• From the House tab, choose “Get knowledge,” then select “Excel workbook” from the dropdown menu.

• Within the Navigator window that seems, choose the “Financials” desk and click on “Remodel Knowledge” to start out knowledge preparation.

3. Knowledge transformation steps

Change Knowledge Sort:

• Choose the “Items Bought” column.
• Go to the Remodel tab, select “Knowledge Sort,” and choose “Entire Quantity.”

• Affirm by deciding on “Change present” to use the change.

Change Textual content Format:

• Choose the “Section” column.
• Within the Remodel tab, click on “Format” and select “UPPERCASE.”

Rename Columns:

• Double-click the “Month Title” column and rename it to “Month.”

Filter Knowledge:

• Click on the dropdown within the “Product” column and uncheck the field subsequent to “Montana” to filter out this discontinued product.

4. Making use of and reviewing adjustments

• Test the “Question Settings” pane beneath “APPLIED STEPS” to see all transformations made.

• To finalise, go to the House tab and choose “Shut & Apply” to use all adjustments.

5. Verifying knowledge

• Guarantee to save lots of your work in case you have not already accomplished so.

• Test the suitable pane to make sure that Energy BI accurately identifies numeric fields (with the Sigma image) and date fields (with the calendar image).

6. Creating Measures and Tables

Create a Measure

• On the House ribbon, choose “New measure.”

• Enter the expression: Whole Items Bought = SUM(financials[Units Sold]) and make sure.

Create a Calendar Desk

• Change to Knowledge view on the left.

• On the House ribbon, choose “New desk”.

• Enter the expression: Calendar = CALENDAR(DATE(20130101), DATE(20141231)).

• Affirm and guarantee a desk of dates from January 1, 2013, to December 31, 2014, has been created.

7. Establishing relationships

• Change to Mannequin view on the left.

• Drag the “Date” discipline from the “financials” desk to the “Date” discipline within the “Calendar” desk to create a relationship between the tables.

Wrapping up

This information has outlined the important steps to arrange knowledge for a Energy BI dashboard, making it appropriate for novices.

The method begins with importing knowledge from a Microsoft Excel file into Energy BI Desktop. As soon as the info is imported, customers are guided by way of a collection of transformations to scrub and format the info.

Key steps embody altering knowledge varieties, formatting textual content, renaming columns, and filtering out undesirable knowledge.

After transformations, the information emphasised reviewing and making use of these adjustments to make sure accuracy. It additionally coated verifying knowledge codecs, comparable to figuring out numeric and date fields.

To boost knowledge evaluation, the information described creating measures and tables, comparable to a “Whole Items Bought” measure and a date-based calendar desk.

Lastly, relationships between completely different tables utilizing the Mannequin view had been established to assist in linking knowledge logically, facilitating extra complete evaluation and reporting.

By following these steps, customers can successfully put together and handle knowledge, laying a stable basis for constructing insightful Energy BI dashboards.