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: I need to generate a conditional index column, so that it only lists the columns that have non-...
  • 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

     

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

 

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    4 years ago

    Thank you very much for your help!