Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Proud to be a Super User!
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
when try your suggestion the flow keep updating none stops, maybe because of the buffle step havent try it out
Proud to be a Super User!
sounds good, will try it out and get back at you with the result
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.