Connecting and Shaping Data in Power BI Desktop

Connecting to Data

1. Open Power BI Desktop:

   • Ensure Power BI Desktop is installed and open.

2. Access Data Sources:

   • Click on Get Data > More in the Home tab to view available data sources.

3. Connect to Web Source:

   • Select Get Data > Web.

   • In the From Web dialog box, paste the following URL:
     basic
     Copy

       https://web.archive.org/web/20240121013927/https://demo.razzi.my/powerbi/The-Best-And-Worst-States-For-Retirement-2021-All-50-States-Ranked-Bankrate.html
     

   • Click OK.

4. Access Web Content:

   • If prompted, select Connect to use anonymous access.

   • The Navigator window will show results, including an HTML table titled "Ranking of best and worst states for retirement." Select this table to preview it.

5. Load or Transform Data:

   • Choose Load to import the table directly or Transform Data to make adjustments before loading.

Shaping Data

1. Open Power Query Editor:

   • Once connected, you can shape the data.

2. Understand Applied Steps:

   • The Applied Steps section in the Query Settings pane records your changes sequentially.

3. Change Data Types:

   • Select the relevant column(s) (hold Shift for adjacent columns or Ctrl for non-adjacent).

   • Right-click on a column header, select Change Type, and choose the desired type.

4. Sort Data:

   • To focus on sunglasses sales, click the arrow next to the Weather header and select Sort Ascending.

5. Remove Rows:

   • Go to Reduce Rows > Remove Rows > Remove Bottom Rows. Enter 10 to remove the bottom ten rows and click OK.

6. Remove Unwanted Columns:

   • Select the headers for Affordability, Crime, Culture, and Wellness columns.

   • Choose Remove Columns from the Manage Columns group.

7. Undo Steps:

   • Undo step 6, click the delete icon next to the last step in the Applied Steps pane.

8. Remove Crime, Culture, and Wellness columns only.

9. Add a new column:

   • In the Custom Column dialog, in the New column name field, enter New score. For the Custom column formula, enter the following data:

   • ([Affordability]+[Weather])/2

10. Rename the Table:

    • In the Properties section of the Query Settings pane, change the title to "Best States for Sunglass Sales."

Combining Data

1. Import State Abbreviation Data:

   • Select New Source > Web from the Home tab.

   • In the From Web dialog box, input: Copy

       https://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations
     

2. Select the Table:

   • In the Navigator window, choose the table titled "Codes and abbreviations for U.S. states, federal district, territories, and other regions." Click OK.

3. Shape the State Codes Table:

   • Keep only the columns Name, Status of region, and ANSI by holding Ctrl and selecting them, then right-click and choose Remove Other Columns.

   • Filter the Status of region column to show only "State."

   • Rename columns to State Name, Status, and Abbreviation.

4. Rename the Table:

   • Rename this table to "State Codes" in the Properties field of Query Settings.

Merging Queries

1. Select the Sunglass Sales Query:

   • Switch to the "Best States for Sunglass Sales" query from the Queries pane.

2. Merge Queries:

   • Click on Merge Queries from the Combine group on the Home tab.

3. Set Up the Merge:

   • In the Merge window, choose the State Codes query and select matching columns: State from the sunglass sales query and State Name from the State Codes query.

   • If prompted, select Ignore privacy levels checks and click OK.

4. Expand Merged Data:

   • A new column called State Codes will appear. Click the expand icon in this column header, select the Abbreviation column, and deselect Use original column name as prefix before clicking OK.

Building Reports

1. Navigate to Report View:

   • In Power BI Desktop, switch to the Report view.

2. Understand the Report Layout:

   • Familiarize yourself with the six main areas: ribbon, canvas, pages tab, Filters pane, Visualizations pane, and Fields pane.

3. Create Visualizations:

   • Drag any field (e.g., State from the "Best States for Sunglass Sales" query) onto the canvas to create a map visualization.

4. Set Up Filters:

   • Use the Filters pane to apply a Top N filter based on the weather ranking to display only the top 10 states.

5. Update Visualization Titles:

   • Change the title of your map to "Top 10 Weather States" in the Format section of the Visualizations pane.

6. Add Additional Visualizations:

   • Create a Column Chart visualization for the names and ranks of the top 10 states, applying the same Top N filter.

7. Insert Additional Elements:

   • Add text boxes, images, and buttons from the Insert group in the Home tab.

8. Save Your Report:

   • Save your work by selecting File > Save.

By following these detailed steps, you'll effectively connect to, shape, combine, and visualize your data in Power BI.