CODEWITHSUNDEEP
powerbipowerquerypowerbi-desktoplookup-tables
alishniftaliyev
alishniftaliyev

Reputation: 5

LOOKUP Value in Power BI for multiple values based on condition

I know that there are various questions related to looking up values in power bi. However, my question is related to looking up multiple values based on condition.

Description of situation:

I have two tables in power bi, one of the tables have product codes, component code, last dates (related to products) and component costs. Another table has the same headers, however last dates are not included because these components are shared by more than one product. These codes were extracted from the first table and they were deleted from the first table. While in the first table, components appeared in front of different product codes and it had its own row, where it had the cost and product code was "Shared". Therefore, in the first table I have them in front of product codes and based on these component codes I want to lookup the cost from the second table to have it as additional column.

I want to lookup values based on one condition, if product has more than one last date, return the component cost to the product with the last date.

First table: enter image description here

Second table: enter image description here

Desired result: enter image description here

Sorry to add pictures, as table formatting did not work.

It would be great if there is any formula for achieving this result. So far, I have tried touse lookupvalue (DAX) and list.positionof (M Query) functions. Both of them gave me error, can you help with this?

Upvotes: 0

Views: 5844

Answers (1)

horseyride
horseyride

Reputation: 21373

Two ways to do it in M

1 -- group on material code and pull out the max of date. Expand. Merge in costs. Add custom column so that if date on row equal max date, then use costs, otherwise zero

Assumes the other table is a query named Table2 as shown in your image

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Material code", type text}, {"Product code", Int64.Type}, {"Last Date", type date}, {"Costs", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Material code"}, {
    {"data", each _, type table [Material code=nullable text, Product code=nullable number, Last Date=nullable date, Costs=nullable number]},
    {"max", each List.Max(_[Last Date]), type date}
    }),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Product code", "Last Date"}, {"Product code", "Last Date"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded data", {"Material code"}, Table2, {"Material code"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Costs"}, {"Costs2"}),
#"Added Custom" = Table.AddColumn(#"Expanded Table2", "Costs", each if [Last Date]=[max] then [Costs2] else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"max", "Costs2"})
in #"Removed Columns"

2 -- if you wanted to get all fancy, you could do this in a single custom column added to Table1 , but it would likely be slow for very large data sets

 #"Added Custom" = Table.AddColumn( #"PriorStepName","CostTable", (i)=>if i[Last Date]= List.Max(Table.SelectRows(#"PriorStepName",each [Material code]=i[Material code])[Last Date]) then Table.SelectRows(Table2, each [Material code]=i[Material code]) [Costs]{0}  else 0)

Upvotes: 0

Related Questions