Create increment value in Power Query based on the occurences of each value in the list

Question

I have a Table of codes in Power Query and the filenames of their pictures. So codes are duplicate values (and more than 5 some times).

I want to create column that contain an increment number based on the occurences of the code value.

For example: Code - Filename - Occurences 001 - 001.jpg - 1 001 - 001_2.jpg - 2 003 - 003.jpg - 1 004 - 004.jpg - 1 004 - anotherpic.jpg - 2 ....

Thanks in Advance

chillin chillin · Accepted Answer · 2020-01-18T12:50:34

If your table of codes looks something like this:

then this code:

let
    initialTable = Table.FromColumns({{"001", "001", "003", "004", "004"}, {"001.jpg", "001_2.jpg" , "003.jpg", "004.jpg", "anotherpic.jpg"}}, type table [Code = text, Filename = text]),
    grouped = Table.Group(initialTable, "Code", {{"toCombine", each Table.AddIndexColumn(_, "Occurrences", 1, 1), type table}}),
    combined = Table.Combine(grouped[toCombine])
in
    combined

should give expected output:

You can delete/replace the first line and replace with whatever you already have. The first line is just to create an example table for demonstrative purposes.

Create increment value in Power Query based on the occurences of each value in the list

1 Answers