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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
ronrsnfld
Community Champion
Community Champion

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

ronrsnfld
Community Champion
Community Champion

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors