Different Methods Apart from Merging to Extract data from the Dimension Table in Power Query

Consider two input tables: the first includes Order ID, Product ID, and Units columns, while the second contains Product ID and Product Name.

Our objective is to bring the Product Name from the second table into the first table.

While the Merge feature can accomplish this, this article aims to examine alternative methods beyond merging.

Method-1:

let

 Source = each Excel.CurrentWorkbook(){[Name=_]}[Content],

 Result = Table.AddColumn(Source("Table1"),"Product Name", each List.First(List.ReplaceMatchingItems({[Product ID]}, Table.ToRows(Source("Table2")))))

in

 Result

The AddColumn function operates on each row in "Table1" to create a new column named "Product Name," utilizing the List.ReplaceMatchingItems function to perform the necessary calculations or transformations in the new column.

Each row's Product ID includes scalar values such as "P1" and "P2". However, the first argument in the List.ReplaceMatchingItems function must be a list. To convert our scalar values into a list, we enclose them in curly braces.

Table.ToRows function transforms the table into a nested list structure, with each row in the dimension table becoming a sublist. This can serve as the second argument, acting as replacements in list form.

in first row of iteration in Table1,

Product ID -> "P1"

The product ID will be verified against the first item (Old Value) in each sublist. If a match is found in any sublist, the second item (New Value) in that sublist will be selected and replaced with the Product ID.

Method-2:

let

 Source = each Excel.CurrentWorkbook(){[Name=_]}[Content],

 Result = Table.AddColumn(Source("Table1"),"Product Name", each Source("Table2")[Product Name]{List.PositionOf(Source("Table2")[Product ID], [Product ID])})

in

 Result

We select the Product ID as a scalar value and determine its position from a list of Product IDs in the dimension table or table2. This position allows us to retrieve the Product Name from the list of Product Names in the dimension table.

Method-3:

let

 Source = each Excel.CurrentWorkbook(){[Name=_]}[Content],

 Result = Table.AddColumn(Source("Table1"),"Product Name", (x) => Table.FirstValue(Table.SelectRows(Source("Table2"), (f) => f[Product ID] = x[Product ID])[[Product Name]]))

in

 Result

In this instance, we apply a filter to the dimension table using the Product ID from the first table, or fact table. This results in a table that contains matching records from the dimension table.

We employed the Projection method to extract only the Product Name column, treating it as a table by using double square brackets.

Subsequently, the Table.FirstValue function assists us in selecting the scalar value of the Product Names.