Forum Discussion
Creating a customer number based on the values of another column
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
Community ChampionPlease 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
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 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 
ronrsnfldSuper User
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- 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})
