Use Count as a Condition in "Custom Column" with Power Query

[goncalogeraldes]

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! 

[sean_w]

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.

[sean_w · Accepted solution]

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!!