Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
nazdravanul
Frequent Visitor

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

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. 

1 ACCEPTED SOLUTION
Interkoubess
Solution Sage
Solution Sage

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...Index.PNG

View solution in original post

2 REPLIES 2
Interkoubess
Solution Sage
Solution Sage

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...Index.PNG

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.