Update a column through a join

The objective of this example is to update the values of a column (DealerPrice) based on the values of a separate column stored in a separate data source.

The Products dimension table is retrieved from a SQL Server database, but over 200 rows do not have dealer price values.

The dealer prices for new, unsold products that are not yet available to the SQL Server database are stored in an Microsoft Access database.

1. Create dedicated data source queries to the SQL Server and Microsoft Access sources.
2. Disable the load for both.

• The Sql.Database() function is used for the SQL Server database
• The Access.Database() function is used for the MS Access database: Access.Database(File.Contents("C:\Finance\Data\DealerPrices.accdb"), [CreateNavigationProperties=true])

• The retrieval queries will reference these sources such that changes in the data sources (for example, different server or file location) can be implemented quickly

1. Create a query that retrieves the product price list from the Microsoft Access table.
2. Give it a distinctive name and disable the load.

Like other examples in this chapter, the Source variable calls the dedicated data source query.

1. Create the Products query from the SQL Server database (products).
   • This query represents the primary source for the Products dimension in the data model.

• Only the Products query is enabled for load to the data model.
• In scenarios with more source inputs and transformations to perform, it may be beneficial to further isolate the inputs into staging queries.

• Table.SelectColumns() retrieves the required columns from the DimProduct table and Table.RenameColumns() adds spaces between column headers.

1. Add an expression that performs a left outer join from the SQL Server-based Products query to the DealerPriceList query on the ProductKey column.
2. Expose the Dealer Price column from the Dealer Price List query to the Products query with a distinct column name.

• Table.NestedJoin() is used to perform the Left Outer Join from the Products query to the DealerPriceList query
• Table.ExpandTableColumn() is used to add the Dealer List Price column from the result of the join

1. Add a conditional column to the query that uses the Dealer List Price column added in step 7 if the Dealer Price column is null.

• Table.AddColumns() is used with a simple if...then
• Currency.Type is specified to avoid an Any type from being loaded to the model as a text value

1. Add two final expressions to exclusively select the conditional price column added in step 6 and rename this column to Dealer List Price.

• Table.SelectColumns() and Table.RenameColumns() are used in step 7.