The Excel Get & Transform enables users to perform SnowFlake merging otherwise known as VLOOKUP in Power Query. SnowFlake merging entails using foreign keys in Fact Table to connect to corresponding unique foreign keys in Dimension Tables to ultimately retrieve sets of values required for further analysis
In the pictures below, we wants to load the Fact Table and Dimension Tables into the power query editor as a separate queries. Note that the data in both Fact and Dimension sheets are stored in Excel table using descriptive names.
To Import the workbook containing the data into Power Query:
- Open a new Excel workbook after closing the workbook that contains the data
- In the Get & Transform Data group of the Data tab of the newly opened workbook, click on Get Data dropdown
- Select From File and From Workbook respectively.
- Browse to select the workbook from the location stored and click on Import
In the Navigation dialogue box, check Select multiple items and check the following tables: Account Manager, Product and Transaction.
- Click on Transform Data. The Power Query Editor automatically opens with all the 3 queries located on the left pane. See below
To merge the Account Manager ID foreign keys in the Transaction query (Fact Table) with the Account Manager ID foreign keys in the Account Manager query (Dimension Table) which will retrieve the corresponding Account Managers name:
- Click on the Account Manager ID column of the Transaction query
- In the Combine group of the Home tab, click on Merge Queries
- In the Merge dialogue box, select Account Manager ID
- In the middle dropdown, select Account Manager
- In the 2 columns below, select Account Manager ID. See below
Notice that the selection matches each other (500 of 500 rows).
- Click OK. Notice, a new column named Account Manager is automatically added to the transaction query with double upward pointed arrows. See below
To unearth the corresponding Account Manager’s name based on the Account Manager ID
- Click on the double upward pointed arrows
- Uncheck Use original column name as prefix and Account Manager ID
- Click OK. Notice that the two queries have been merged and corresponding Account Manager’s name was retrieved based on the Account Manager ID. See below.
To merge the Product ID foreign keys in the Transaction query (Fact Table) with the Product ID foreign keys in the Product query (Dimension Table) which will retrieve the corresponding Product names:
- Click on the Product ID column of the Transaction query
- In the Combine group of the Home tab, click on Merge Queries
- In the Merge dialogue box, select Product ID
- In the middle dropdown, select Product
- In the 2 columns below, select Product ID. See below
Notice that the selection matches each other (500 of 500 rows).
- Click OK. Notice, a new column named Product is automatically added to the transaction query with double upward pointed arrows. See below
To unearth the corresponding Product names based on the Product ID
- Click on the double upward pointed arrows
- Uncheck Product ID
- Click OK. Notice that the two queries have been merged and corresponding Product Name was retrieved based on the Product ID. See below.
The above steps is essentially how to perform SnowFlake merging in Excel Power Query and the data can be used for further analysis. Remember to register four our In-depth Microsoft Excel and Power BI training for professionals and business owners