In the Power BI skills course Adam Gordon references a txt file he listed with the episode for "Work with data and queries". That file not available through the course and I wanted to see if I can get a copy of this file as it contains steps for performing data imports and transformation information that would be use to have.
Thank you
@Dennis-OHearn , I hope all is well. My apologies, as that file, along with a few others are supposed to be part of the episode notes .zip download file. I am working on getting it added to the .zip file currently, so they will be there going forward.
Thank you for asking and making me aware of their absence.
I am pasting the contents of the file you are looking for into the area below, so you have access to it in the meantime.
If you have any other questions, or if I can be of any additional assistance as you continue your Power BI journey, please be in touch as needed.
Cheers,
=============================
Work with data & queries
Step 1: Connect via WEB connector:
https://www.bankrate.com/retirement/best-and-worst-states-for-retirement/
Step 2: Transform loaded data
a. Sort Weather Rank column by ascending
b. Reduce rows --> Remove bottom rows --> 10
c. Remove the Crime, Culture, & Wellness columns
d. Rename Table (FUN IN THE SUN)
Step 3: Combine data
a. Import the state abbreviations data into Power Query Editor, select New Source --> Web from the New Query group on the Home tab of the ribbon.
https://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations
Select the table Codes and abbreviations for U.S. states, federal district, territories, and other regions, and then select OK.
b. Remove all columns except for Name and status of region, Name and status of region, and ANSI. (Column 1, Column 2, Column 4)
c. Drop down the arrow next to the Column 2 header, and select Filters > Equals. In the Filter Rows dialog box, drop down the Enter or select a value field next to equals and select State. Select OK.
d. Rename the columns to make more sense, State name (Column 1), Status (Column 2), and Abbreviation (Column 4), by right-clicking the column headers and selecting Rename.
e. Rename table (State Codes)
Step 4: Merge the Queries
There are two primary ways of combining queries: MERGE & APPEND
One or more columns to add to existing query --> MERGE
Additional rows of data to add to an existing query --> APPEND
To merge the queries, switch to the FUN IN THE SUN query by selecting it from the Queries pane on the left side of Power Query Editor.
Select Merge Queries from the Combine group in the Home tab of the ribbon.
In the Merge window, drop down the field to select State Codes from the other queries available.
Select the column to match from each table, in this case State from the FUN IN THE SUN query and State Name from the State Codes query.
*** If you get a Privacy levels dialog, select Ignore privacy levels checks for this file and then select Save. Select OK.
A new column called State codes appears on the right of the FUN IN THE SUN table.
All the columns from the merged table are condensed into the State codes column.
You can expand the merged table and include only the columns you want.
To expand the merged table and select which columns to include, select the Expand icon in the column header.
In the Expand dialog box, select only the Abbreviation column; Deselect Use original column name as prefix, and then select OK.
CLOSE & APPLY
Thank you, I appreciate the reply. I found most of your information within the MS Query notes. I have not validated that your example is pulled all from the document but in any case I will need to spend time with all the BI documentation. Thank you again for your support and clear instructions you share. I enjoy your sessions as I am also doing the CISSP curriculum.
Regards