Hello,
I have a file containing an ID column. I want to add an index to each row, restarting the index at 1 whenever the ID changes. To be clearer, I have attached an example with the expected result.
Thank you for your help!
Remi
Hello,
I have a file containing an ID column. I want to add an index to each row, restarting the index at 1 whenever the ID changes. To be clearer, I have attached an example with the expected result.
Thank you for your help!
Remi
Please upload your file
Have you tried using DataMentor/EDNA AI tool built in the EDNA Platform to help solve your issues?
Thanks Keith
Primary key.xlsx (10.8 KB)
Keith,
No, I don’t know DataMentor/EDNA AI tool, but if you have the solution, I’m interested.
Thank you.
P.S. I just downloaded the file
Hi @Remi10,
I am on my phone so I’ll describe the process to achieving the desired result.
I hope this is helpful
An example, based upon the PQE-solution provided by Melissa:
PBIX : Index per customer example.pbix (37.9 KB)
let
Source = Excel.Workbook(File.Contents("I:\BI Power BI Enterprise DNA\x Forum respons\2024\SalesExample.xlsx"), null, true),
SalesT_Table = Source{[Item="SalesT",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(SalesT_Table,{{"Customer", type text}, {"Product", type text}, {"Sales Amount", type number}}),
#"Grouped Rows (All Rows)" = Table.Group(#"Changed Type", {"Customer"}, {{"Step1 GroupBy", each _, type table [Customer=nullable text, Product=nullable text, Sales Amount=nullable number]}}),
#"Add Custom Column Index" = Table.AddColumn(#"Grouped Rows (All Rows)", "Step2 Add Index per group", each Table.AddIndexColumn([Step1 GroupBy], "Index", 1, 1, Int64.Type)),
#"Expanded Step2 Index per group" = Table.ExpandTableColumn(#"Add Custom Column Index", "Step2 Add Index per group", {"Customer", "Product", "Sales Amount", "Index"}, {"Customer.1", "Product", "Sales Amount", "Index"}),
#"Changed Type Index text" = Table.TransformColumnTypes(#"Expanded Step2 Index per group",{{"Sales Amount", type number}, {"Index", type text}}),
#"Added Custom Desired Result" = Table.AddColumn(#"Changed Type Index text", "Desired result", each [Customer] & "_" &[Index]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom Desired Result",{"Step1 GroupBy", "Customer.1"})
in
#"Removed Columns"
Great, thank you very much for your help
Thanks you for your help