Index column

[Syndicate_Admin]

Good morning, I need help!

How can I in Power Query create a conditional index column, I explain:

I need to generate a conditional index column, so that it only lists the columns that have non-zero values, so in the image data it should have from 1 to 5 and the values that are 0 do not have a number. Can anyone help me?

[PhilipTreacy · Accepted solution]

Hi @Syndicate_Admin  @nataliaguzman02 

Download example PBIX file with the following code

This query does what you want

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjA3MDBQitWJVkImzYxMDdBZENLI2ACbBqikoRkOyVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cportafolio_t.Rendimento Mercado" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cportafolio_t.Rendimento Mercado", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Cportafolio_t.Rendimento Mercado] <> 0 then List.Count(List.RemoveMatchingItems(List.RemoveLastN(#"Added Index"[Cportafolio_t.Rendimento Mercado],List.Count(#"Added Index"[Cportafolio_t.Rendimento Mercado])-[Index]),{0}) ) else ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

Regards

Phil

[Syndicate_Admin]

Thank you very much for your help!