Forum Discussion
Super UserUse Count as a Condition in "Custom Column" with Power Query
Hi there, I am trying to create a custom column that if the type of the transaction is of type "Financiamento", it divides the value by the count of "Financiamentos". I have tried to use DAX formulas, through "Calculated Columns" and now with Power Query. Currently the code is as follows but it is not working:
let
Source = Table.Combine({MovimentosCBLPocas, MovimentosCBLSVTV}),
#"Merged Queries" = Table.NestedJoin(Source, {"Conta+Empresa"}, Financiamentos,
{"Conta+Empresa"}, "Financiamentos", JoinKind.LeftOuter),
#"Expanded Financiamentos" = Table.ExpandTableColumn(#"Merged Queries", "Financiamentos",
{"Contratado", "Tipo"}, {"Financiamentos.Contratado", "Financiamentos.Tipo"}),
#"Added Custom" = Table.AddColumn(#"Expanded Financiamentos", "UtilizadoTotal",
let
Source = Table.Combine({MovimentosCBLPocas, MovimentosCBLSVTV}),
#"Merged Queries" = Table.NestedJoin(Source, {"Conta+Empresa"}, Financiamentos,
{"Conta+Empresa"}, "Financiamentos", JoinKind.LeftOuter),
#"Expanded Financiamentos" = Table.ExpandTableColumn(#"Merged Queries",
"Financiamentos", {"Contratado", "Tipo"}, {"Financiamentos.Contratado",
"Financiamentos.Tipo"}),
#"Grouped Rows" = Table.Group(#"Expanded Financiamentos", {"Conta+Empresa",
"Financiamentos.Tipo"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows",
in
#"Added Custom"
Do you have any suggestion? Thanks in advance!
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*
4 Replies
Are you attempting to have this done as a "rolling count" or is this a count of total transactions = "Financiamento"?
If the second, I actually had a similar question a couple hours ago and found this article helpful.https://exceloffthegrid.com/power-query-percent-of-total/
- goncalogeraldes
It'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 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*
