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
Hi Jalal_1988
#1 'Power Query Chalenge 6' won't help people who search this site for existing solutions. Could you revise the title of this discussion please?
Something like 'Power Query Add new balance type when Balance = 0, by Customer' would reflect the scenario
when the balance reach to zero change the letter
also when the costumer change also letter changed
#2 Except if I missunderstood, what you highlighted in yellow for MIKE doesn't follow the above logic. Could you confirm the green values are the expected ones for MIKE (or re-explain please):
#3 What's expected after letter Z? A1, AA...?
I WANT JUST LIKE THE YELLOW
I WANT TO EXTRACT THE TIME TO WANT KNOW EACH COSTUMER IN HOW MANY TIME ITS BALANCE REACH TO ZERO WHEN BY MATERILS
,
- #3 Response: I wan to extract the days which the costumer balance is not zero,
#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: