Add an Index for Duplicate Values using Power Query

Question

I have a dataset that has duplicate Invoice Number values.

I need help generating an index column ("Occurrences") to count the number of occurrences of the "InvoiceNo".

Any guidance would be greatly appreciated.

InvoiceNo	Occurrence
100011	1
100012	1
100013	1
100011	2
100011	3
100012	2
100014	1

I have tried using code suggested from a similar thread, but it is generating an error. The error says "We cannot convert the value 100011 to type list". However, I'm not sure that the syntax of the code is correct anyway. Appreciate any guidance that you can give. TIA.

let
    initialTable = Table.FromColumns(InvoiceNo, type table [Occurrence = text]),
    grouped = Table.Group(initialTable, "InvoiceNo", {{"toCombine", each Table.AddIndexColumn(_, "Occurrences", 1, 1), type table}}),
    combined = Table.Combine(grouped[toCombine])
in
    combined

R vE · Accepted Answer · 2025-10-22 07:29:59Z

2

As an alternative. Lets assume your table with invoice numbers is called "Table1", then this code should work.

let
    Source =Excel.CurrentWorkbook(){[Name="Table1"]}[Content],  
    AddIndex =Table.AddIndexColumn(Source,"Index",0,1,Int64.Type),
    AddOccurrance =Table.AddColumn(
        
        AddIndex,
        "Occurrance",
        each Table.RowCount(
            let
                Inv =[InvoiceNo],       
                Idx =[Index]            
            in
                Table.SelectRows(AddIndex,  
                    each [InvoiceNo] = Inv and [Index] <= Idx
                                             
                                             
                    )
        )
    ),

    RemoveCol =Table.RemoveColumns(AddOccurrance,{"Index"})                     
in
    RemoveCol

edited Oct 22 at 7:29

answered Oct 21 at 11:17

R vE

805 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user31722869 Oct 22 at 11:15

Thanks for your response. Unfortunately, I'm getting the error which is preventing me from accessing "Table1". I'm unsure how I permit this connection. "Formula.Firewall: Query 'Invoice Data' (step 'Added Custom') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

R vE Oct 22 at 11:23

Don't really know. Works for me without issues. Did you try in a new workbook and build the query from scratch. It isn't that complicated.

user31722869 Oct 22 at 13:32

Thanks, I resolved the Formula.Firewall issue - was a simple privacy issue to be changes in the settings.

user31722869 Oct 22 at 14:10

Thank you for your help - I resolved the privacy issue. I am calling the data from a separate file, so once I changed from Excel.CurrentWorkbook() to Excel.Workbook(File.Contents("C:\Path\Invoice Data.xlsx"), null, true){[Name="Table1"]}[Data] it worked perfectly. Thanks for assistance.

horseyride · Accepted Answer · 2025-10-21 12:26:32Z

1

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Sort", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"InvoiceNo"}, {{"data", each Table.AddIndexColumn(_, "Occurances", 1, 1, Int64.Type), type table}}),
    #"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Occurances", "Sort"}, {"Occurances", "Sort"}),
    #"Sorted Rows" = Table.Sort(#"Expanded data",{{"Sort", Order.Ascending}})
    in #"Sorted Rows"


add column ..  index column for later resorting
right click InvoiceNo column, group by ... using "all rows" operation
In the generated code from above step, replace the _ with Table.AddIndexColumn(_, "Occurances", 1, 1, Int64.Type)
expand the grouped column
resort based on index

obviously the index and resort are optional

edited Oct 21 at 12:26

answered Oct 21 at 10:26

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

1 Comment

user31722869 Oct 22 at 14:12

Thanks for your response - I got this partially working - the index wasn't counting properly - probably my lack of understanding rather than the wrong solution. I'll play with it again later and see if I can get working. In meantime the alternative solution provided is working for me.

Collectives™ on Stack Overflow

Add an Index for Duplicate Values using Power Query

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related