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