Primary key ID column

Power Query
1

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

2

Uploading: Primary key.xlsx…

3

Please upload your file

Have you tried using DataMentor/EDNA AI tool built in the EDNA Platform to help solve your issues?

Thanks Keith

4

Primary card
Primary key.xlsx (10.8 KB)

5

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

6

Hi @Remi10,

I am on my phone so I’ll describe the process to achieving the desired result.

  1. In the PQE make sure the Key column is selected
  2. On the ribbon, choose Group By, from the available aggregations select All Rows. Press OK
  3. A step is added to your query. Look inside the formula bar and locate the expression: each _
  4. Update this into: each Table.AddIndexColumn(_, “i”, 1,1)
  5. Now look for the type table and remove the record, that follows it […]
  6. Expand the fields from the Grouped Column, including the new column “i”
  7. Convert “i” and “Key”, to text and Merge these columns.

I hope this is helpful

2 Likes
7

@Remi10

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"

image

8

Great, thank you very much for your help

9

Thanks you for your help

10

Datamentor is part of the EDNA platform

image
image1838×215 25.7 KB

check it out.

thanks
Keith