Multiply value based on variable in same column

[AlleynMacGregor]

Hi,

I have a basket of goods, with multiple units of measure, "each", carton quantity's "C6", "C4", "C8" etc.. and a pallet unit of measure "PLT S".

The unit of measures are all in one column, with the corresponding QTY for the unit of measure in another column. See sample data below:

itemNo	code	qtyperUnitofMeasure
Carrots	EACH	1
Carrots	C6	6
Carrots	PLT	5
Carrots	PLT S	49
Potatoes	C12	12
Potatoes	EACH	1
Potatoes	PLT S	50
Beetroot	PLT S	27
Beetroot	EACH	1
Beetroot	C8	8

In the above sample data, the code column will can have the "C8", "C6" etc.. in a different row order.

The carton quantity for "C8" has 8 "each" and a "PLT S" quatity of 50 has 50 cartons. I need this calcuated to how many "each" per "PLT S". i.e. 8 x 50 = 400 "each ", meaning one "PLT S" has 400 "each".

The lowest unit of measure will always be an "each".

Any help greatly appricated.

[6677028]

let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    #"Sorted Rows" = Table.Sort(Source,{{"Item", Order.Ascending}, {"Code", Order.Ascending}}),

    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each if [Code] ="PLT S" then [Value] else null),

    #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"}),

    #"Added Custom1" = Table.AddColumn(#"Filled Up", "Custom.1", each [Value]*[Custom]),

    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if Text.Start([Code],1)="C" then [Custom.1] else null),

    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Value", "Custom", "Custom.1"}),

    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.2", "Each final"}})

in

    #"Renamed Columns"

[AlleynMacGregor]

Hi,

In my example the "PLT S" is how many "Cx" are on a pallet. So using the table for Carrots the "PLT S" is 49, and the cartons is "C6", meaning I have 294 EACh on 1 x "PLT S". 

This is what I need to calculate for each "PLT S", how many "EACH" on a "PLT S".

[6677028]

you recheck. According to the table above, c8 should be 8x 27 right?