Visit number of individual customers

[kimknguyen]

Dear Chandoo.org's Experts,
Please help...
Attached is a list of customers and the days that they visit our shop. I need to calculate the visit number (1st, 2nd, and so on) of individual customers. I can set it up in Excel but since there are >100,000 records and counting, the calculation gets slower and slower.
I would like to do it in Power Query but need your help.
Thank you,
kim

 

[AlanSidman]

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Grouped Rows" = Table.Group(Source, {"Customer"}, {{"Data", each _, type table [Date Time=datetime, Customer=text, #"Visit #"=any]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "count", each Table.AddIndexColumn([Data], "Visits",1,1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}), #"Expanded count" = Table.ExpandTableColumn(#"Removed Columns", "count", {"Date Time", "Visits"}, {"Date Time", "Visits"}) in #"Expanded count"

 

[kimknguyen]

Alan,
Thank you very much!
Please help with adding another column to calculate the number of days between visits.
I forever appreciate your time and expertise.
Sincerely,
Kim

 

[p45cal]

In the attached, an adaptation of Alan's solution.

 

[kimknguyen]

Dear 45cal,
Thank you and Alan for your help in solving the visits and the time between visits. I am trying to understand the steps and incorporate them into my database (which has many columns). It seems I am missing the formula set up for fnDayCount, since when I Invoke Customer Function, the Function Querry dialogue box is grayed out.
Please point the way,
Thank you so much.
P.S: I spent many hours Googling how to solve the problem of calculating the visits and time between visits in Power Querry but still can't solve it.
Without Alan's and your help, I wouldn't be able to solve it.
Sincerely,
Kim N.