Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

Index column

Good morning, I need help!

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

nataliaguzman02_0-1636559362416.png

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's avatar
    PhilipTreacy
    4 years ago

    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

     

2 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    4 years ago

    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