Dynamic data indexation in Power Query

[mkuzner]

Hello lovely Power Query people,

I have a problem in POwer Query with how to create an index for categories in a column, the same way it can easily be done in Excel using a little upgraded knowledge of COUNTIF function.

From an ERP system that I am using, I get daily export (every day with some additional data) in Excel with categories in one of the columns. I want to assign them an index in another column and of course every day it can happen that a new category, not used before, can pop up, which has to start a new index for that specific category. 

Here is a link to a sample data. Dates in column A, categories in column B, and "an Excel solution with COUNTIF" in column C. Basically what I did in Excel in column C should be done in Power Query. 

If anybody has any suggestions, I will be more than happy to read them.

Thank you in advance.

Regards, Marko

[spinfuzer · Accepted solution]

Group By Category and then Operation All Rows.

Change each _ to each Table.AddIndexColumn(_,"Index",1)

Either delete all the type information or add Index = number.  Leave the curly braces alone {{ }}.  If you do not delete the column information or add the necessary column information then you will not be able to expand the table column through the GUI.

Copy and paste below into an empty query for example.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtS31DcyMDJR0lFKVIrVAQkYGsBEkmAihhhqjDDUGMNEkqEiRsbouozMcYvAzDGyxC0CNDkWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"category", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"category"}, {{"Rows", each Table.AddIndexColumn(_,"Index",1), type table [date=nullable date, category=nullable text]}})
in
    #"Grouped Rows"

[mkuzner]

Hello spinfuzer!

Works like a charm. 🙂

Where can a person learn this kind of code tweaking? 

Regards, Marko