Forum Discussion
Use Count as a Condition in "Custom Column" with Power Query
- 5 years ago
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*
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*
Perfect! Thank you very much! Just had to do some small changes and it worked!!