Forum Discussion
campbellmurphy
Helper I
Helper ICreate column based on variety of variables in multiple columns and rows
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
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.
6 Replies
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"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"
campbellmurphyHi 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 +-/*.
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.
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"