Forum Discussion

mwm39's avatar
mwm39
Frequent Visitor
2 years ago
Solved

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 so...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

CoreyP's avatar
CoreyP
Solution Sage
2 years ago

Do you have an item master table which contains what the carton size should be?

  • CoreyP's avatar
    CoreyP
    Solution Sage
    2 years ago

    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?

    • mwm39's avatar
      mwm39
      Frequent Visitor
      2 years ago

      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 

  • mwm39's avatar
    mwm39
    Frequent Visitor
    2 years ago

    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