Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Atinder
Helper III
Helper III

Lookup value from another table - if no match give me an error

Hello,

I am trying to lookup a valume from another table. In this case I want to Lookup Sales UOM from Item card table into Bin Content  table. If we they are not matching give me " Wrong UOM". 

 

I am trying to find how many Bins  I have with Wrong Unit of measure beacuse If i only sell them into MASTERCASE. I should not have any PCS on hand in the warehouse. 

 

 

Atinder_0-1706555948828.png

Atinder_1-1706555959234.png

 

Thank you

2 ACCEPTED SOLUTIONS
tackytechtom
Super User
Super User

Hi @Atinder ,

 

How about this?

tackytechtom_7-1706558374207.png

 

Here the M code that you can paste into the advanced editor. Note the steps on the right that might give you a clue what it's going on 🙂 

tackytechtom_6-1706558334906.png

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkjNU9JRMjTQNTTSdTQEM4FEgHOwUqwOmqwRkGkEkvV1DA5xDXJ2DHZVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SKU = _t, #"BIN CODE" = _t, qty = _t, UOM = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SKU", type text}, {"BIN CODE", type text}, {"qty", Int64.Type}, {"UOM", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"SKU"}, #"Item Card", {"sku"}, "Item Card", JoinKind.LeftOuter),
    #"Expanded Item Card" = Table.ExpandTableColumn(#"Merged Queries", "Item Card", {"Sales UOM"}, {"Item Card.Sales UOM"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Item Card", "Custom", each if Text.Lower([UOM]) <> Text.Lower([Item Card.Sales UOM]) then "Wrong UOM" else null)
in
    #"Added Custom"

 

let me know if this solves your issue. 

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

Anonymous
Not applicable

Hi @Atinder ,

You can create a calculated column as below in the table 'Bin content' to get it, please find the details in the attachment.

Column = 
VAR _suom =
    CALCULATE (
        MAX ( 'Item Card'[Sales UOM] ),
        FILTER ( 'Item Card', 'Item Card'[Sales UOM] = 'Bin content'[UOM] )
    )
RETURN
    IF ( ISBLANK ( _suom ), "Wrong UOM", _suom )

vyiruanmsft_0-1706599463533.png

Best Regards

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi @Atinder ,

You can create a calculated column as below in the table 'Bin content' to get it, please find the details in the attachment.

Column = 
VAR _suom =
    CALCULATE (
        MAX ( 'Item Card'[Sales UOM] ),
        FILTER ( 'Item Card', 'Item Card'[Sales UOM] = 'Bin content'[UOM] )
    )
RETURN
    IF ( ISBLANK ( _suom ), "Wrong UOM", _suom )

vyiruanmsft_0-1706599463533.png

Best Regards

tackytechtom
Super User
Super User

Hi @Atinder ,

 

How about this?

tackytechtom_7-1706558374207.png

 

Here the M code that you can paste into the advanced editor. Note the steps on the right that might give you a clue what it's going on 🙂 

tackytechtom_6-1706558334906.png

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkjNU9JRMjTQNTTSdTQEM4FEgHOwUqwOmqwRkGkEkvV1DA5xDXJ2DHZVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SKU = _t, #"BIN CODE" = _t, qty = _t, UOM = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SKU", type text}, {"BIN CODE", type text}, {"qty", Int64.Type}, {"UOM", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"SKU"}, #"Item Card", {"sku"}, "Item Card", JoinKind.LeftOuter),
    #"Expanded Item Card" = Table.ExpandTableColumn(#"Merged Queries", "Item Card", {"Sales UOM"}, {"Item Card.Sales UOM"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Item Card", "Custom", each if Text.Lower([UOM]) <> Text.Lower([Item Card.Sales UOM]) then "Wrong UOM" else null)
in
    #"Added Custom"

 

let me know if this solves your issue. 

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.