Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jrosa5
New Member

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 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?

jrosa5_0-1663086924198.png

 

1 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

ronrsnfld_0-1663120105394.png

 

 

View solution in original post

5 REPLIES 5
HotChilli
Super User
Super User

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

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

UCNID
 262
 312
 363
 910
 910
 913
 2147
 2147
 2162

 

After

UCNID
 38294262
 38295312
 38296363
 38297910
 38297910
 38298913
 382992147
 382992147
 383002162

=#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})

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

ronrsnfld_0-1663120105394.png

 

 

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

UCNID
 262
 312
 363
 910
 910
 913
 2147
 2147
 2162

 

After

UCNID
 38294262
 38295312
 38296363
 38297910
 38297910
 38298913
 382992147
 382992147
 383002162

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors