Forum Discussion
Super UserUse Count as a Condition in "Custom Column" with Power Query
Got it. See if this works:
let Source = Table.Combine({MovimentosCBLPocas, MovimentosCBLSVTV}), MergeFinanciamentos = Table.NestedJoin(Source, {"Conta+Empresa"}, Financiamentos, {"Conta+Empresa"}, "Financiamentos", JoinKind.LeftOuter), ExpandFinanciamentos = Table.ExpandTableColumn(MergeFinanciamentos, "Financiamentos", {"Contratado", "Tipo"}, {"Financiamentos.Contratado", "Financiamentos.Tipo"}), Grouped = Table.Group(ExpandFinanciamentos, {"Conta+Empresa", "Financiamentos.Tipo"}, {{"Count", each Table.RowCount(_), Int64.Type}}), Filter = Table.SelectRows(Grouped, each ([Financiamentos.Tipo] = "Financiamentos")), MergeBack = Table.NestedJoin(ExpandFinanciamentos, {"Conta+Empresa", "Financiamentos.Tipo"}, Filter, {"Conta+Empresa", "Financiamentos.Tipo"}, "Counts", JoinKind.LeftOuter), ExpandTotals = Table.ExpandTableColumn(MergeBack, "Counts", {"Count"}, {"Count"}), Divide = Table.AddColumn(ExpandTotals, "Eval", each [Count] / [TotalUsed], Int64.Type), ReplaceInvalids = Table.ReplaceValue(Divide, each null, each [SingleValue],Replacer.ReplaceValue,{"Eval"}) in ReplaceInvalidsWithout seeing sample data and sample expected end result and going based solely off your initial code block, this is the best I've got. It's close to what you had in your original post. I think you were on the right track (assuming this solves your need).
*Fixed an oversight where I didn't do the division*
Super UserIt's actually a bit more complicated than that. I have a table that has the description for each financing operation that the company has. Some are labelled as "Financiamentos" while others have other names that do not matter for the analysis. There is also the "TotalUsed" column, "TotalAllowed" column, "SingleValue" and if the type is "Financiamento" I want to display the value of the "TotalUsed" divided by the count of financing operations for that bank. If not, just display the "SingleValue". Kinda complicated explaining this, hope its not too confusing.
Got it. See if this works:
let
Source = Table.Combine({MovimentosCBLPocas, MovimentosCBLSVTV}),
MergeFinanciamentos = Table.NestedJoin(Source, {"Conta+Empresa"}, Financiamentos,
{"Conta+Empresa"}, "Financiamentos", JoinKind.LeftOuter),
ExpandFinanciamentos = Table.ExpandTableColumn(MergeFinanciamentos, "Financiamentos",
{"Contratado", "Tipo"}, {"Financiamentos.Contratado", "Financiamentos.Tipo"}),
Grouped = Table.Group(ExpandFinanciamentos, {"Conta+Empresa", "Financiamentos.Tipo"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
Filter = Table.SelectRows(Grouped, each ([Financiamentos.Tipo] = "Financiamentos")),
MergeBack = Table.NestedJoin(ExpandFinanciamentos, {"Conta+Empresa", "Financiamentos.Tipo"}, Filter, {"Conta+Empresa", "Financiamentos.Tipo"}, "Counts", JoinKind.LeftOuter),
ExpandTotals = Table.ExpandTableColumn(MergeBack, "Counts", {"Count"}, {"Count"}),
Divide = Table.AddColumn(ExpandTotals, "Eval", each [Count] / [TotalUsed], Int64.Type),
ReplaceInvalids = Table.ReplaceValue(Divide, each null, each [SingleValue],Replacer.ReplaceValue,{"Eval"})
in
ReplaceInvalids
Without seeing sample data and sample expected end result and going based solely off your initial code block, this is the best I've got. It's close to what you had in your original post. I think you were on the right track (assuming this solves your need).
*Fixed an oversight where I didn't do the division*
- goncalogeraldes
Perfect! Thank you very much! Just had to do some small changes and it worked!!