Forum Discussion
Solved
LOOP to Look up the value in M Query
Hi, I have a following data table: I need to create a column called FINAL VALUE with certain rules in the power query editor: - The FINAL VALUE is created based on the COUNTRY - If VA...
Hi, qsong
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may go to 'Query Editor'=>'Add Column'=>'Custom Column', input the following codes.
let country=[Country],date=[Date], tab = Table.SelectRows( #"Changed Type", each [Country]=country and [Date]<date and [Value]<100 ) in if Table.RowCount(tab)>0 then if [Value]>100 then Table.Max(tab,"Date")[Value] else [Value] else if [Value]<100 then [Value] else 100Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, qsong
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may go to 'Query Editor'=>'Add Column'=>'Custom Column', input the following codes.
let
country=[Country],date=[Date],
tab = Table.SelectRows(
#"Changed Type",
each [Country]=country and [Date]<date and [Value]<100
)
in
if Table.RowCount(tab)>0
then
if [Value]>100
then Table.Max(tab,"Date")[Value]
else [Value]
else
if [Value]<100
then [Value]
else 100
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.