Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Generate the same reference numbers for reports submitted by a company in the same day

Hi

I'd like to have a calculated column that generates unique reference numbers for reports submitted by companies with the following requirement. 

1. When a company submitted more than one reports on the same day, the report reference would be the same. 

For additional context, I have unique reference numbers for each reports which I use for relationships. 
 Thank you in advance for any help you can provide. 

cheers

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous , Create a rank column on truncated date column

     

    new columns =

    Date = datevalue([Datetime])

     

    Rank = rankx(Table, [Date],,asc,dense)

     

    or

     

    Rank = rankx(filter(Table, [Company] = earlier([Company]) ) , [Date],,asc,dense)

     

    You can rank in power query

    Rank in Power Query? Power Query Table.AddRankColumn : https://youtu.be/bnB1rgAhZaw