Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Thank you
Solved! Go to Solution.
Hi @Atinder ,
How about this?
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 🙂
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! | |
#proudtobeasuperuser | |
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 )
Best Regards
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 )
Best Regards
Hi @Atinder ,
How about this?
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 🙂
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! | |
#proudtobeasuperuser | |