Forum Discussion
Adding Column based on a List and results of costum function
Good Morning
I've a Table called Score_Dati_Bilancio in witch i would like to execute this function:
let Fx_Calcola_Score = (Nome_Colonna,Colonna_Indicatore, Settore) =>
let
Min_Colonna = "Min_"&Nome_Colonna,
TabellaFiltrata_Min = Table.SelectRows(Calcola_Minimo_Massimmo_Dati_Settore, each [Column3] = Settore),
Fx_Estrai_Min_Indica_Sett = if Table.RowCount(TabellaFiltrata_Min) > 0 then Record.Field(TabellaFiltrata_Min{0},Min_Colonna) else null,
Max_Colonna = "Max_"&Nome_Colonna,
TabellaFiltrata_Max = Table.SelectRows(Calcola_Minimo_Massimmo_Dati_Settore, each [Column3] = Settore),
Fx_Estrai_Max_Indica_Sett = if Table.RowCount(TabellaFiltrata_Max) > 0 then Record.Field(TabellaFiltrata_Max{0},Max_Colonna) else null,
Fx_Differenza_Max_Min = Fx_Estrai_Max_Indica_Sett-Fx_Estrai_Min_Indica_Sett,
Calcolo_Score = if Colonna_Indicatore =null then 0 else if Fx_Estrai_Ordine_Di_Grandezza(Nome_Colonna) = ">"
then if Fx_Estrai_Min_Indica_Sett = Fx_Estrai_Max_Indica_Sett
then 100
else
Number.Round(
((Colonna_Indicatore-Fx_Estrai_Min_Indica_Sett)
/
Fx_Differenza_Max_Min)*100,2)
else if Fx_Estrai_Min_Indica_Sett = Fx_Estrai_Max_Indica_Sett
then 100
else
100-Number.Round(
((Colonna_Indicatore-Fx_Estrai_Min_Indica_Sett)
/
Fx_Differenza_Max_Min)*100,2)
in
Calcolo_Score
in Fx_Calcola_Score
but i've to execute the same function for all the Column of the table adding a number of columns with the results of the function row by row and column by column.
I know that i can pass to the function a List of List but i'm not able to modify the function to do that.
I have the list with all the possible combination of the variables to pass in the function.
This is an example of the List:
Lista_Per_Formule_Score_Dati_Bilancio =
{{"Column4","13,62469673","Communication Services"}
{"Column4","40,58528441","Consumer Discretionary"}
{"Column4","26,29401389","Consumer Discretionary"}
{"Column4","41,94496844","Consumer Discretionary"}
{"Column4","31,17785124","Consumer Staples"}
{"Column4","105,0996445","Energy"}
{"Column4","114,1381401","Energy"}
{"Column4","16,82760614","Health Care"}
{"Column4","21,09438176","Health Care"}
{"Column4","19,94786981","Health Care"}
{"Column4","26,43846708","Industrials"}
{"Column4","34,71899107","Industrials"}
{"Column4","74,25650913","Utilities"}
{"Column4","11,77347591","Utilities"}
{"Column4","16,23375629","Utilities"}
{"Column5","null","Communication Services"}
{"Column5","40,75620503","Consumer Discretionary"}
{"Column5","18,25142962","Consumer Discretionary"}
{"Column5","72,01697449","Consumer Discretionary"}
{"Column5","30,58142254","Consumer Staples"}
{"Column5","null","Energy"}
{"Column5","226,1763395","Energy"}
{"Column5","null","Health Care"}
{"Column5","19,24137254","Health Care"}
{"Column5","14,60258108","Health Care"}
{"Column5","44,69384885","Industrials"}
{"Column5","null","Industrials"}
{"Column5","null","Utilities"}
{"Column5","null","Utilities"}
{"Column5","null","Utilities"}}
in the real one i reach Column25 with 15 different combinations of data inside but the number could change in the future and the List it's able to change itself automatically.
is there a way to do that? can you please help me?
1 Reply
Perhaps it's better with recursion. Function could be like
fn = ( lst as list, tbl as table) => if List.Count(lst) = 0 then tbl else @fn( List.Skip(lst), let current = lst{0}, Min_Colonna = "Min_"& current{0}, // next steps t = finalStep in t ),where lst is you list of lists. Instead of single list parameters you use current for the single list in iteration and when current{0}, current{1}, current{2} where applicable.
Function is called as fn( listOfLists, tableToTransform)
