unique index for each code

[ssaouiy59]

Hello everyone,

I'm a new user of DAX query, i'm more comfortable with excel lol.

Anyway, i would like to give a unique index for each code.

The problem is i can do it on excel with this formula : =SI(E3=E2;B2;B2+1)

But on Power BI it's another issue lol 

Here is the resultat that i would like to have, if anyone have a suggestion, I'm all ears

Note that i have just the "code" column 

code	ID
AEM-0001	1
AEM-0001	1
AEM-0003	2
AEM-0003	2
A0M-0002	3
AEM-0001	4
AEM-0001	4

[v-yangliu-msft]

Hi  @ssaouiy59 ,

Here are the steps you can follow：

1. In Power query. Add Column – Index Column – From 1.

2. Create calculated column.

Index1 =
var _index1=
MAXX(FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])),[code])
var _index2=
MAXX(FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])-1),[code])
return
IF(
    _index1=_index2,0,1)

ID =
SUMX(FILTER(ALL('Table'),'Table'[Index]<=EARLIER('Table'[Index])),'Table'[Index1])

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[yaowen]

Here's maybe the DAX code that does it:

ADDINDEX = SUBSTITUTEWITHINDEX('Table' ,"ID", VALUES('Table'[code]), [code],ASC)

Please try and see what the result is.

[daXtreme]

@ssaouiy59 

You can't do this in DAX as DAX has no concept of row order. You have to use Power Query.

[ssaouiy59]

Hmm, ok ! So i tried to create a column from an exemple (using my code column) but it doesn't recognize it

Same with the conditionnal column function (i can't specify rows)

Do you have any idea please ? Should i create a specific query ?

Thanks for your help

[daXtreme · Accepted solution]

Here's the M code that does it:

// Codes
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnT11TUwMDBUUIrVwcEzxsIxAHOMcGsC8WIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"code", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "code"}),
    #"Added Custom" = Table.AddColumn(
        #"Reordered Columns", "PreviousCode", 
        each Table.SelectRows(
            #"Reordered Columns",
            (row) => row[Index] = _[Index] - 1
        )[code]
    ),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"PreviousCode", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Conditional Column" = Table.AddColumn(#"Extracted Values", "Custom", each if [code] = [PreviousCode] then 0 else 1),
    #"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "CodeDoesNotEqualPrevCode"}}),
    RunningTotal = Table.AddColumn(
        #"Renamed Columns", "Running Total", 
        each List.Sum(List.FirstN(#"Renamed Columns"[CodeDoesNotEqualPrevCode], [Index] + 1))
    ),
    #"Removed Columns" = Table.RemoveColumns(RunningTotal,{"Index", "PreviousCode", "CodeDoesNotEqualPrevCode"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"Running Total", "code"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"Running Total", "ID"}})
in
    #"Renamed Columns1"

Just paste it into the Advanced Editor in Power Query and then inspect the steps.

This is the output of the above: