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

Question

Existing column &nbsp; &nbsp;Required ouptut on custom column&nbsp;BAB &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp;BAB &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1BAB &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2CIC &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0CIC &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1CIC &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2CIC &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3CIC &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4DDE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0DDE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp;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.&nbsp;

interkoubess · Accepted Answer

Hi nazdravanul,&nbsp;I copied and pasted your data in my model ( I called Data), and this is the Power Query code ( with Advanced Editor) :&nbsp;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....&nbsp;Ninter...

Forum Discussion

Related content

Create measure to return value from column when condition met for two other columns

Create conditional index column

Create columns group

Conditional Column with Multiple Conditions

Create Conditional Column Based Upon Two Other Columns