Forum Discussion
Power Query Add new balance type when Balance = 0, by Customer'
In a nutshell:
#1 Add an Index ([RID]) to the source table
#2 Groub By [CUSTOMER]
#3 For reach [CUSTOMER] add a Balance ID ([BID]) - done with https://learn.microsoft.com/en-us/powerquery-m/list-generate
#4 Combine all Customer nested tables ([DATA]) = 'CustomerBalanceId'
#5 Build a table with numbers from 1 to Max of all [BID] and the correspoding char. (1 = A, 2 = B, ...) = 'TableBalanceType'NB: If > 26 (letter Z) next will be AB, AC...
#6 Join 'CustomerBalanceId' & 'TableBalanceType' on [BID]
#7 Sort [RID] Ascending ** Remember that Grouping & Merging operations cannot guarantee an ordered set of records. Hence adding and Index at the beginning and sorting that Index at the end
#3 Response: I wan to extract the days which the costumer balance is not zero
I don't understand (sorry), post an example (1 Customer is enough) please.
In your initial request you talk about Letters. In the event where we reach Letter Z I wondered what should be the next Letter(s) and you talk about days now. I'm at lost
I have it working in the meantime:
if you reach this letter like example its ok and share the file ,
Thank you
In a nutshell:
#1 Add an Index ([RID]) to the source table
#2 Groub By [CUSTOMER]
#3 For reach [CUSTOMER] add a Balance ID ([BID]) - done with https://learn.microsoft.com/en-us/powerquery-m/list-generate
#4 Combine all Customer nested tables ([DATA]) = 'CustomerBalanceId'
#5 Build a table with numbers from 1 to Max of all [BID] and the correspoding char. (1 = A, 2 = B, ...) = 'TableBalanceType'NB: If > 26 (letter Z) next will be AB, AC...
#6 Join 'CustomerBalanceId' & 'TableBalanceType' on [BID]
#7 Sort [RID] Ascending ** Remember that Grouping & Merging operations cannot guarantee an ordered set of records. Hence adding and Index at the beginning and sorting that Index at the end