SOLVED

Power Query Add new balance type when Balance = 0, by Customer'

Brass Contributor

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

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

 

Sample.png


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

Hey @Jalal_1988 

 

#1 Thank you 

#2 Understood

#3 What's expected after letter Z? A1, AA...?

 

#3 Response: I wan to extract the days which the costumer balance is not zero,

@Jalal_1988 

 

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

Sample.png

My friend please do not mix them, i just want this letter like explain before and you reach it, about next step (days)، i will doing it,
if you reach this letter like example its ok and share the file ,
Thank you
best response confirmed by Jalal_1988 (Brass Contributor)
Solution

@Jalal_1988 

 

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

1 best response

Accepted Solutions
best response confirmed by Jalal_1988 (Brass Contributor)
Solution

@Jalal_1988 

 

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

View solution in original post