New column in Power Query with 'follow-up' number

[Anonymous]

Hello community members, 

I have the following question/problem:

In Power Query, I would like to insert a 'follow-up' number column that reflects the number of invoices (column StartDate) per ClientCode. But...:

1st: When the number in the column 'Costs' is 0 or less, the specific row should not get a follow-up number.

2nd: In some cases the column StartDate shows 2 or more times the same date. In that case, the column InvoicedDate should be used to find out what is the 'earliest' StartDate row is.

In the picture below I have made the calculated column yellow and the two times we have the same StartDate orange. 

I also attached the example PBIX to this post with the same information as in the picture... 

https://we.tl/t-ioOFRx5jnC  

Who can help me with the correct formula for the new column in Power Query?

Hope to hear soon from you. 

Best regards, Sander 

[lbendlin]

check the Advanced Editor output.  These kinds of functions can often not be used (or get botched) in the visual query editor.

[lbendlin · Accepted solution]

Table.AddColumn(#"Type gewijzigd", "FuN", (k)=>
if k[Costs]<0 then null else Table.RowCount(Table.SelectRows(#"Type gewijzigd",each (([StartDate]<k[StartDate] or [StartDate]=k[StartDate] and [InvoicedDate]<k[InvoicedDate]) and [ClientCode]=k[ClientCode] and [Costs]>=0)))+1)

[Anonymous]

Hi @lbendlin 

Today, I tried to convert your formula in a new report and I used the same column names. As a result, I get the word 'function' instead of a number. Any idea what I am doing wrong? 

[Anonymous]

@lbendlin Thanks for your help! 

[lbendlin]

Please explain the 5-7-8 sequence for ClientCode 10045. Shouldn't that be 5-6-7 ?

let
    Bron = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdFLDoUwCAXQvTCWBCjUupbG/W/DtPhB7XtOaAcncAO1gqKgEC8wASum80+k5m+rsE4VmD+sNStuP/pKt8ntEiyjjjNot8L/LXqItx1lkFbNrQUrvzLMbkuwaWB7hHLs4ZprN2vSptO1s7gyQ3vSHE9BoVV+Wyx0ruFmB305UbhFBBnzE8+232LdAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, InvoicedDate = _t, ClientCode = _t, Costs = _t, FollowUpNumber = _t]),
    #"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"StartDate", type date}, {"InvoicedDate", type date}, {"ClientCode", Int64.Type}, {"FollowUpNumber", Int64.Type}, {"Costs", type number}}),
    #"Added Custom" = Table.AddColumn(#"Type gewijzigd", "FuN", (k)=>
 if k[Costs]<0 then null else Table.RowCount(Table.SelectRows(#"Type gewijzigd",each ([StartDate]<k[StartDate] and [ClientCode]=k[ClientCode] and [Costs]>=0)))+1)
in
    #"Added Custom"

[Anonymous]

Hi @lbendlin 

The 5-7-8 was a typo, it should be 5-6-7. 

I tried the formula and it works almost perfect. Only in the following situation it goes wrong; 

I added a new row with the same StartDate. As a result, I get the same follow-up (volgnummer) number. But when the StartDate is the same, the InvoicedDate should be used to see what is the earliest InvoicedDate. That row should get the 'lowest' number. I tried to visualise the problem in the picture below... Is it possible for you to make a small adjustment in the formula so this issue is solved? 

Cheers, Sander