M Languague Optimization Sugggestion Specific Case

[StefanoGrimaldi]

hey guys, I have a little query Im trying to optimize and looking for point and tips here have already done some progress but need some help futher optimizing it, any suggestion would be great (The "Personalizado agregado" part its actually the only one making a significant impact on the procesing act the momment): 

let
  Origen = #"Facturación por articulo",
  #"Consultas combinadas" = Table.NestedJoin(Origen, {"Fecha Contabilización - Copia"}, Tasa, {"Fecha Contabilización"}, "Tasa", JoinKind.LeftOuter),
  #"Tasa expandido" = Table.ExpandTableColumn(#"Consultas combinadas", "Tasa", {"Tasa"}, {"Tasa.1"}),
  #"Personalizado agregado" = Table.AddColumn(#"Tasa expandido", "Monto Articulo (Descento Doc Aplicado) (Pesos Dominicanos)", each let
    doc = [Numero Documento],
    cantidadlineas = Table.RowCount(Table.SelectRows( #"Tasa expandido", each [Numero Documento] = doc)),
    totaldoc = List.Sum(Table.SelectRows( #"Tasa expandido", each [Numero Documento] = doc)[#"Total Articulo (Descuento Aplicado) (RD)"]),
    porcientoxlinea = ([#"Total Articulo (Descuento Aplicado) (RD)"] / totaldoc),
    Resultado = [#"Total Articulo (Descuento Aplicado) (RD)"] - (porcientoxlinea*(([#"% Descuento Factura (RD)"])/100)*totaldoc)
in
    Resultado),
  #"Personalizado agregado 1" = Table.AddColumn(#"Personalizado agregado", "Monto Articulo (Descento Doc Aplicado) (Dollar)", each [#"Monto Articulo (Descento Doc Aplicado) (Pesos Dominicanos)"] / [Tasa.1]),
  #"Tipo de columna cambiado" = Table.TransformColumnTypes(#"Personalizado agregado 1", {{"Monto Articulo (Descento Doc Aplicado) (Pesos Dominicanos)", type number}, {"Monto Articulo (Descento Doc Aplicado) (Dollar)", type number}}),
  #"Columnas quitadas" = Table.RemoveColumns(#"Tipo de columna cambiado", {"% Descuento Factura (RD)", "Total Articulo (Descuento Aplicado) (RD)", "Fecha Contabilización - Copia", "Tasa.1"})
in
  #"Columnas quitadas"

[watkinnc]

I would wrap the inputs of your list arithmetic in List.Buffer. Feeding buffered lists to your list functions is a HUGE optimization that needs to be taught in kindergartens worldwide.

--Nate

[mahoneypat · Accepted solution]

Two suggestions to try, since you are referencing the previous step more than once:

  #"Personalizado agregado" = Table.AddColumn(#"Tasa expandido", "Monto Articulo (Descento Doc Aplicado) (Pesos Dominicanos)", each let
    doc = [Numero Documento],
    filtered = Table.SelectRows( #"Tasa expandido", each [Numero Documento] = doc),
    cantidadlineas = Table.RowCount(filtered),
    totaldoc = List.Sum(filtered[#"Total Articulo (Descuento Aplicado) (RD)"]),
    porcientoxlinea = ([#"Total Articulo (Descuento Aplicado) (RD)"] / totaldoc),
    Resultado = [#"Total Articulo (Descuento Aplicado) (RD)"] - (porcientoxlinea*(([#"% Descuento Factura (RD)"])/100)*totaldoc)
in
    Resultado),

 #"Tasa expandido" = Table.Buffer(Table.ExpandTableColumn(#"Consultas combinadas", "Tasa", {"Tasa"}, {"Tasa.1"})),

Pat

[StefanoGrimaldi]

when try your suggestion the flow keep updating none stops, maybe because of the buffle step havent try it out 

[StefanoGrimaldi]

sounds good, will try it out and get back at you with the result