Forum Discussion
Power BI- multiple values
Hi All,
I have a table with items and specification (Length, width, Height, weight) and Carton used . Ideally items should show same specification and Carton used in all transactions.
However sometime due to system /user error (user selecting different carton) , some items appear to have numerous spec and carton used. For example:
Item L H W Carton Used
101 1 1 1 W1
102 2 2 2 W2
103 3 3 3 W3
101 1 1 2 W1
102 2 2 2 W1
In above all items 101,102,103 have correct specification from line 1 to 3. However line 4 and 5 showing incorrect value (highligheted in red). How can I identify records which is deviating from its standard specification. We have nearly 3500 items and roughly 25000 lines generated every day. Any help would be highly appreciated. Thanks
Hi mwm39 ,
You can apply the below codes in the Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRQsbhhkqxOiAJIyAHGYcbQSWMgRxkHG4MlUA2xgivUUCJWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, L = _t, H = _t, W = _t, #"Carton Used" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}, {"L", Int64.Type}, {"H", Int64.Type}, {"W", Int64.Type}, {"Carton Used", type text}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Corrected W", each if [L]=[H] and [L]=[W] and [H]=[W] then [H] else Number.Round(([L]+[H]+[W] )/3)), #"Added Custom" = Table.AddColumn(#"Added Custom1", "Corrected Carton Used", each if [L]=[H] and [L]=[W] and [H]=[W] then "W"&Number.ToText([H]) else "W"&Number.ToText(Number.Round(([L]+[H]+[W] )/3))) in #"Added Custom"Best Regards
7 Replies
Do you have an item master table which contains what the carton size should be?
Or are you calculating what the carton size should be? For instance, highest number of occurences for carton size is considered what it should be?
For instance, highest number of occurences for carton size is considered what it should be?
this is 100% accurate, highest occurences is what it should be
Yes we have master files for volume specification but not for carton because for example customer order two items together than caarton would be different if they order items seperately. For Carton I just need to identify all Carton types being used for items. For example
item Qunatity Carton type
101 1 W1
101 1 W1
101 1 W3
I want to identify that if with 1 quantity majority used W1 and what other Cartons were used as well. Hope its clear. Thanks
Yes we have master files for volume specification but not for carton because for example customer order two items together than caarton would be different if they order items seperately. For Carton I just need to identify all Carton types being used for items. For example
item Qunatity Carton type
101 1 W1
101 1 W1
101 1 W3
I want to identify that if with 1 quantity majority used W1 and what other Cartons were used as well. Hope its clear. Thanks
You can use a RANKX function to rank the item and quantity by count of carton type. Then you can filter or highlight rows where the rank does not equal 1.
Hi mwm39 ,
You can apply the below codes in the Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRQsbhhkqxOiAJIyAHGYcbQSWMgRxkHG4MlUA2xgivUUCJWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, L = _t, H = _t, W = _t, #"Carton Used" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}, {"L", Int64.Type}, {"H", Int64.Type}, {"W", Int64.Type}, {"Carton Used", type text}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Corrected W", each if [L]=[H] and [L]=[W] and [H]=[W] then [H] else Number.Round(([L]+[H]+[W] )/3)), #"Added Custom" = Table.AddColumn(#"Added Custom1", "Corrected Carton Used", each if [L]=[H] and [L]=[W] and [H]=[W] then "W"&Number.ToText([H]) else "W"&Number.ToText(Number.Round(([L]+[H]+[W] )/3))) in #"Added Custom"Best Regards
