Find first occurrence of a flag

[mleech]

Hi, 

I have a table which lists the customer number, ebill, trans date and trans time.  I am trying to determine the first time the ebill column is "Y".  I thought maybe I need to use a MIN calculation but am not sure how to do this.

Thanks

[Chris_Botha]

I would appreciate some help.

I am just starting to use Power Query in Excel.

I have a column with bank code.

OPEN BALANCE is a code that occures many times, but I need to remove rows of all occurences after the first one.

I was thinking the easiest would be to create a new custom column with will  be checking the CODE column and returning "First" when it finds the first OPEN BALANCE code and maybe "More" when it finds the rest of the OPEN BALANCE entries and then "Other" for all other codes.  Then I can easily just use filter to remove the "More" ones.

Kind regards

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Ashish_Mathur]

Hi,

Do you want to know that per Cust_key or across all Cust-keys?  Also, what result do you want - Date or Time?  Share your expected result.  Share the link from where i can download your PBI file.

[mleech]

Hi, 

Yes sorry if my question was not detailed enough.  I am looking to flag or indicate the first date a custmer has a "Y" under Ebill field.  I would like to know that per unique Cust_key.

What I want to create is a field "First Ebill" and a Y if its the first occurence and a "N" if its not.  So in the example of CUST_KEY 0019068, I would like to have a "Y" in the "First Ebill" column in the row with the TRANS_DATE 9/28/2016 and "N" for all other rows for that CUST_KEY

[Ashish_Mathur]

Hi,

I am assuming you want this as a calculated column formula (and not a measure).  Am i correct?  If yes, then share the link from where i can download your PBI file.

[mleech]

Yes I want a calculated column and not a measure.  I have attached a link to my file.

Thanks,

https://www.dropbox.com/s/zeo903xmir04yh2/test0319.pbix?dl=0

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

=if('Ebill test'[EBILL]="Y",if(CALCULATE(MIN('Ebill test'[TRANS_DATE]),FILTER('Ebill test','Ebill test'[CUST_KEY]=EARLIER('Ebill test'[CUST_KEY])&&'Ebill test'[EBILL]="Y"))='Ebill test'[TRANS_DATE],"Y","N"),BLANK())

Hope this helps.

[mleech]

That worked!!! 

Thank you so much

[Ashish_Mathur]

You are welcome.