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.
Thanks for your input.
Due to some reason , I want to do in power query only. Can you please help how to do as I tried many options in the M query but not succeeded ? or merge queries option also ?
Community Championfor power query:
let
currentRow = [Owner_id = _[Owner_id], Comp_code = _[Comp_code], Creation_date <= _[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), 1)
else
List.FirstN(matches, 1)
in
result
and for merge:
- On the Home tab, click Merge Queries.
- Choose the first table from the first drop-down list, and the second table from the second drop-down list.
- Select the columns you want to join on (in your case, Owner_id, Comp_code and Creation_date).
- Choose the join type. In your case, you would use a Left Outer join to keep all from first and matching from second.
I am getting error on the M query it shows error against date field.
Also the merge queries option how to add the additional logic .
let
currentRow = [CO_OWNER_ID = _[CO_OWNER_ID], Company Code = _[Company Code],Document_Create_Date <= [Document Create Date]],
matches = Table.SelectRows(VIM_BUMAP, each Record.HasFields(currentRow, _)),
result = if List.IsEmpty(matches) then
List.FirstN(Table.SelectRows({VIM_BUMAP}, each [CO_OWNER_ID] = _[CO_OWNER_ID] and [Company Code] = null and [Document Create Date] = null), "1")
else
List.FirstN(matches,"1")
in
resultIf removes date then it is no syntax error but the result column is error with below.
Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=[Table]
Type=[Type]- rubayatyasmin
modified 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 AddCustomin 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.