Forum Discussion
Solved
Creating a customer number based on the values of another column
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 jus...
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
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 |
ronrsnfld
Super User
Super UserTry 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