Create conditional index column based on row values - at Querry level

[nazdravanul]

Existing column    Required ouptut on custom column

BAB                     0  

BAB                     1

BAB                     2

CIC                      0
CIC                      1
CIC                      2
CIC                      3
CIC                      4

DDE                     0
DDE                     1

This is a very important issue for our business and despite the simple form I couldn't solve it in Power BI, until now. It needs to be done in M Querry, in order to allow a subsequent join with another table / data source, so a DAX solution will not help. 

[Interkoubess · Accepted solution]

Hi @nazdravanul,

I copied and pasted your data in my model ( I called Data), and this is the Power Query code ( with Advanced Editor) :

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

So you can replace the source and then copy and paste the rest....

Ninter...

[nazdravanul]

Thank you very much - with a bit of tinkering, in order to adjust it to my existing querry, this worked like a charm! 🙂