Forum Discussion

Donalson's avatar
Donalson
Regular Visitor
4 years ago
Solved

PowerQuery

Estimados señores,

En Excel nosotros tenemos la formula countif, como usamos esta formula en la tabla de BI?

Me gustaria contar los valores repetidos en la tabla de forma ascendente de acuerdo a su ubicación: ej:

Countif (A$1:A2;A2), donde la columna "A" = "Nombre"

NombreCountif
Juan1
Pedro1
Ricardo1
Juan2
Ricardo2
Need Help
  • Syndicate_Admin's avatar
    Syndicate_Admin
    4 years ago

    Hola

    Este código M funciona

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ipNzFOK1YlWCkhNKcoHs4IykxOLUiBsuDRcMBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    Partition = Table.Group(#"Added Index", {"Number"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index", "Index1"}, {"Index", "Index1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

    Espero que esto ayude.

    Untitled.png

1 Reply

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    4 years ago

    Hola

    Este código M funciona

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ipNzFOK1YlWCkhNKcoHs4IykxOLUiBsuDRcMBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    Partition = Table.Group(#"Added Index", {"Number"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index", "Index1"}, {"Index", "Index1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

    Espero que esto ayude.

    Untitled.png