Forum Discussion
Merge Vs List function for my scenario where I need to map a value from another query
Hi, rraja2k2
The goal of your problem is to assign a value from Query2's "BU" column to Query1 based on certain conditions. You have correctly identified two approaches:
- Using Power Query's built-in merge queries functionality
- Using Power Query M functions, specifically list functions
In terms of performance, neither approach is necessarily optimal for very large datasets, as both involve row-by-row operations. Power Query is best for transforming medium-sized datasets, while a database-level operation would be more efficient for large datasets. It may be worth considering moving this operation to the source system (SAP HANA DB) if possible, as databases are designed for handling this kind of large-scale operations more efficiently than Power BI.
If you find performance degrades with either approach, it may be helpful to simplify the model or reduce the amount of data being processed.
Community Championmodified M
let
Source = Query1,
AddCustom = Table.AddColumn(Source, "BU", each
let
currentRow = [Owner_id = [Owner_id], Comp_code = [Comp_code], Creation_date <= DateTime.From([Creation_date])],
matches = Table.SelectRows(Query2, each Record.HasFields(currentRow, _)),
result =
if List.IsEmpty(matches) then
List.FirstN(Table.SelectRows(Query2, each [Owner_id] = [Owner_id] and [Comp_code] = null and [creation_date] = null)[BU], 1)
else
List.FirstN(matches[BU], 1)
in
result
)
in
AddCustom
in merger query,
to add the additional logic, use conditional column.
Learn more about conditional column.
Add a conditional column - Power Query | Microsoft Learn
I am creating custom column in the query1 but the current code shows me the columns from Query1 instead of query2. Still the date is showing as invalid identifier.
- rubayatyasmin
you are creating custom column in query 1 so it is obvious that it will show columns from query1. nit query 2. And power Query uses #date and date funstions to identify date.
#date - PowerQuery M | Microsoft Learn
Date functions - PowerQuery M | Microsoft Learn