Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to 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
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
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 |
=#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
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 |
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
41 | |
24 | |
21 | |
20 | |
13 |
User | Count |
---|---|
129 | |
61 | |
60 | |
28 | |
20 |