Create column based on variety of variables in multiple columns and rows

[campbellmurphy]

Hi Everyone,

I would like to add a column onto the table below which returns a different value depending on the operator that is available e.g. for:

• KPI 635 the answer is 7800/2600 = 3.  This is the Balance Sum of KPI 1 divided by the Balance Sum of KPI 3
• KPI 2393 the answer is 2500 - 2700 = -200. This is the Balance Sum of KPI 2 minus the Balance Sum of KPI 4

It's worth noting that:

• KPI Number is a unique value
• No KPI referenced in column A or B will not have a Balance Sum
• Column A will always be the numerator and B the denominator for division
• Formula1 for our purpose is = ((2 + 3) / 3)

The obvious way to me seems to be to use SWITCH to allow the column to go through all the different operators. The issue that I'm having is in using columns A & B to reference their respective KPIs Balance Sum.

I greatly appreciate you taking the time to read my issue and am open to any suggestions you might have.

KPI Number	KPI Name	Department	Balance Sum	Operator	A	B	Column
1	Total Sales	Overall	7800				7800
2	Total Sales	Body Shop	2500				2500
3	Total Sales	Pre-Delivery	2600				2600
4	Total Sales	New Vehicles	2700				2700
635	Sales per advisor FTE	Body Shop		/	1	3	3
2393	Unsold hours	Service		-	2	4	-200
715	Total Income	Administration		+	2	3	5100
2372	Uncharged at effective rate	Body Shop		*	3	4	7020000
2373	Total Income	Service		Formula1	2	3	1.666667

Sample Data:
Create Column Example PBI
Create Column Example Excel 

[Anonymous]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZBBa8MwDIX/ishxa1kTN013XNkKu2yDdLuEHoytNgYnKo6b0X8/OWk3FoOFJXif35OrKkmTWbIjLy2U0mLH03uPTlrLXbFeLPi6nv2sSrJIvSF9gbKmE/dZPtWLSP/hcP6M1rDJJSCrKbKMkDf8hi+sjRrHrJgiK5FzO8jhhA6k7k1HDra7l0lCPg9cYWkxLiQeQ8bPtiOroaazCxYlut4oHIF58ORaDkCR5r8BX1tFTVA96ca0pvNOekPtiN1fsZtPkQ0+qpbuiBqkBzwcUHn+CWAO46R3A30z5hdE7Pwv6ZZcc7Yy/XPe/wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"KPI Number" = _t, #"KPI Name" = _t, Department = _t, #"Balance Sum" = _t, Operator = _t, A = _t, B = _t]),
    mt = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"Balance Sum", Int64.Type}}),
    op= [#"+"=Value.Add,#"*"=Value.Multiply,#"/"=Value.Divide,#"-"=Value.Subtract],
    #"Aggiunta colonna personalizzata" = Table.AddColumn(mt, "map", each  let r= Record.FieldOrDefault(op,[Operator],[A]/[B]+1)(mt[Balance Sum]{[A]-1},mt[Balance Sum]{[B]-1}), R=try r in if R[HasError] then R[Error][Detail][Value]??[Balance Sum]  else r)
in
    #"Aggiunta colonna personalizzata"

[Anonymous]

or

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZBBa8MwDIX/ishxa1kTN013XNkKu2yDdLuEHoytNgYnKo6b0X8/OWk3FoOFJXif35OrKkmTWbIjLy2U0mLH03uPTlrLXbFeLPi6nv2sSrJIvSF9gbKmE/dZPtWLSP/hcP6M1rDJJSCrKbKMkDf8hi+sjRrHrJgiK5FzO8jhhA6k7k1HDra7l0lCPg9cYWkxLiQeQ8bPtiOroaazCxYlut4oHIF58ORaDkCR5r8BX1tFTVA96ca0pvNOekPtiN1fsZtPkQ0+qpbuiBqkBzwcUHn+CWAO46R3A30z5hdE7Pwv6ZZcc7Yy/XPe/wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"KPI Number" = _t, #"KPI Name" = _t, Department = _t, #"Balance Sum" = _t, Operator = _t, A = _t, B = _t]),
    mt = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"Balance Sum", Int64.Type}}),
    op= [#"+"=Value.Add,#"*"=Value.Multiply,#"/"=Value.Divide,#"-"=Value.Subtract,Formula1=(x,y)=>x/y+1,#""=(x,y)=>null],
    #"Aggiunta colonna personalizzata" = Table.AddColumn(mt, "map", each  Record.Field(op,[Operator])(mt[Balance Sum]{[A]-1??0},mt[Balance Sum]{[B]-1??0}))
in
    #"Aggiunta colonna personalizzata"

[campbellmurphy]

Hi Rocco,

The new map column appears to be correct for the KPI rows 635, 2393, 715 & 2372.  It Doesn't account for the Formula1 Operator e.g. (A + B) / B.  There are a number of rows in my actual dataset that have specific formulas rather than using a single operator like +-/*.

[Anonymous · Accepted solution]

Ok. Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZBBa8MwDIX/ishxa1kTN013XNkKu2yDdLuEHoytNgYnKo6b0X8/OWk3FoOFJXif35OrKkmTWbIjLy2U0mLH03uPTlrLXbFeLPi6nv2sSrJIvSF9gbKmE/dZPtWLSP/hcP6M1rDJJSCrKbKMkDf8hi+sjRrHrJgiK5FzO8jhhA6k7k1HDra7l0lCPg9cYWkxLiQeQ8bPtiOroaazCxYlut4oHIF58ORaDkCR5r8BX1tFTVA96ca0pvNOekPtiN1fsZtPkQ0+qpbuiBqkBzwcUHn+CWAO46R3A30z5hdE7Pwv6ZZcc7Yy/XPe/wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"KPI Number" = _t, #"KPI Name" = _t, Department = _t, #"Balance Sum" = _t, Operator = _t, A = _t, B = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "form", each try if [Operator]="Formula1" then Text.From([A]) & "/"& Text.From([B])&"+1" else #"Modificato tipo"[Balance Sum]{[A]-1} & [Operator] & #"Modificato tipo"[Balance Sum]{[B]-1} otherwise [Balance Sum]),
    #"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Aggiunta colonna personalizzata", "expr", each Expression.Evaluate([form]))
in
    #"Aggiunta colonna personalizzata1"

This is a solution that fits the example you have proposed. I'm not sure it will work in more general cases. If you want a more robust solution, you should provide more comprehensive examples or better explain the structure of your data and the logic behind it.

[campbellmurphy]

Perfect! I really appreciate your help.

[Anonymous]

I'm pretty sure that's not exactly what you want. But just to start ...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZBBa8MwDIX/ishxa1kTN013XNkKu2yDdLuEHoytNgYnKo6b0X8/OWk3FoOFJXif35OrKkmTWbIjLy2U0mLH03uPTlrLXbFeLPi6nv2sSrJIvSF9gbKmE/dZPtWLSP/hcP6M1rDJJSCrKbKMkDf8hi+sjRrHrJgiK5FzO8jhhA6k7k1HDra7l0lCPg9cYWkxLiQeQ8bPtiOroaazCxYlut4oHIF58ORaDkCR5r8BX1tFTVA96ca0pvNOekPtiN1fsZtPkQ0+qpbuiBqkBzwcUHn+CWAO46R3A30z5hdE7Pwv6ZZcc7Yy/XPe/wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"KPI Number" = _t, #"KPI Name" = _t, Department = _t, #"Balance Sum" = _t, Operator = _t, A = _t, B = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "form", each try if [Operator]="Formula1" then #"Modificato tipo"[Balance Sum]{[A]-1} & "/" & #"Modificato tipo"[Balance Sum]{[B]-1}&"+1" else #"Modificato tipo"[Balance Sum]{[A]-1} & [Operator] & #"Modificato tipo"[Balance Sum]{[B]-1} otherwise [Balance Sum]),
    #"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Aggiunta colonna personalizzata", "expr", each Expression.Evaluate([form]))
in
    #"Aggiunta colonna personalizzata1"