Forum Discussion
Solved
Power Query Add new balance type when Balance = 0, by Customer'
Hello my Friends
I have problem in power query thank you for your help
I want to create a column like L power query,
when the balance reach to zero change the letter and also when the costumer change also letter changed, also sorted by date,
this file is balance statements for two costumers,
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 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
7 Replies
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...?- Thank you L Z. NO THE GREEN CAN NOT HELP ME
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
,
