Creating a customer number based on the values of another column

[jrosa5]

I am trying to create a column that incrementally increases by one based on the values of another column in my query. The dataset I'm working with has non-unique customers within it, but I cannot just pull out the unique customers because there is unique data for each observation in the set. What I am trying to do is increase by one by customer (I have sorted the data so that non-unique customer observations are next to each other in the dataset). There is a unique customer identifier that I use to verify if the observation needs a new number or should have the number as the same value above it.

The excel formula currently being used looks something like this: =IF(Customer Number Current = Customer Number Previous, Value, Value + 1).

I tried adding an Index column and referencing that to compare the Customer Numbers, but my problem is for the "Value" field, that represents this newly created value and I am unsure how to call a column I am in the middle of creating in M. Below is a screenshot of what I want the data to look like from an old Excel file. Any suggestions?

[HotChilli]

Please add some detail.

We'll need to see what the data looks like before (post it as text not a picture please).

If you can post a before and after (with an explanation of how to get from A to B) then we can progress this

[jrosa5]

Currently, I am pulling this data in weekly and doing tranformations in Excel. The "ID" column is part of the data I pull in, but I need to create a new "UCN" for each unique ID. To do this I populate the first cell in the UCN column with my starting index (in this case 38294) and then in the second cell write the following IF statement to finish the calculation: =IF(B3=B2,A2,A2+1)

I want to automate this transformation using Power Query, but am struggling to translate the Excel IF statement into M.

Before

UCN	ID
	262
	312
	363
	910
	910
	913
	2147
	2147
	2162

After

UCN	ID
38294	262
38295	312
38296	363
38297	910
38297	910
38298	913
38299	2147
38299	2147
38300	2162

[wdx223_Daniel]

=#table({"UCN","ID"},List.Accumulate(PreviousStepName[ID],{{},[]},(x,y)=>let a=x{1}&Record.AddField([],Text.From(y),"") in {x{0}&{{38293+Record.FieldCount(a),y}},a}){0})

[ronrsnfld · Accepted solution]

Try this:

let

//change next two lines to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="IDtbl"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),

//You don't indicate where you get the first value from
//   so you'll have to code it somehow.
    #"First UCN" = 38294,

//Generate list of UCN's and associated IDs
    UCNs = List.Generate(
        ()=>[ucn=#"First UCN", id=#"Changed Type"[ID]{0}, idx=0],
        each [idx] < Table.RowCount(#"Changed Type"),
        each [ucn=if #"Changed Type"[ID]{[idx]+1} = [id] then [ucn] else [ucn]+1,
                id=#"Changed Type"[ID]{[idx]+1}, idx = [idx]+1],
        each {[ucn],[id]}),

//convert to a table
    tbl = Table.FromColumns(
            {List.Alternate(List.Combine(UCNs),1,1,1)} &
            {List.Alternate(List.Combine(UCNs),1,1,0)},
            type table[UCN=Int64.Type, ID=Int64.Type])
in
    tbl

[jrosa5]

Hello, currently I am doing this calculation every time in Excel. The sample data is below. But, there is a customer identifier column that is prepopulated in the dataset I'm pulling in (ID) that I use to create a UCN. When I do this calculation in Excel currently, I plug the first value in I want to use (in this case 38294) into the first cell for the UCN and in the second start an IF formula that is used the rest of the way down where =IF(B3=B2,A2,A2+1). I am trying to make this action replicatable in Power Query, but am struggling with the writing of the IF statement in M. 

Before

UCN	ID
	262
	312
	363
	910
	910
	913
	2147
	2147
	2162

After

UCN	ID
38294	262
38295	312
38296	363
38297	910
38297	910
38298	913
38299	2147
38299	2147
38300	2162