¡Hola! ¿Cómo puedo hacer esto en el Editor de Power Query? Necesito crear un ranking por mes de ciertos valores. Aquí está la tabla que estoy tratando de recrear. Necesito calcular la columna Ranking. FechaÍndiceClasificación1/1/202110511/1/202110321/1/20218541/1/20219532/1/202110722/1/202110812/1/20219832/1/2021964

¿@jeamps01 Descargar archivo PBIX de ejemplo Esta consulta lo hace let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lEyNDBVitVBEzJGF7LAUGQJETFC1maOKWSBLmSJKWKmFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Index = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Index", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index.1", 0, 1, Int64.Type), #"Sorted Rows" = Table.Sort(#"Added Index",{{"Date", Order.Ascending}, {"Index", Order.Descending}}), #"Grouped Rows" = Table.Group(Table.Buffer(#"Sorted Rows"), {"Date"}, {{"Count", each _, type table [Date=nullable date, Index=nullable number, Index.1=number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index.2", 1, 1, Int64.Type)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Index", "Index.1", "Index.2"}, {"Index", "Index.1", "Index.2"}), #"Sorted Rows1" = Table.Sort(#"Expanded Custom",{{"Date", Order.Ascending}, {"Index.1", Order.Ascending}}), #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows1",{"Index.1"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Index.2", "Ranking"}}) in #"Renamed Columns" Saludos Phil

Rango avanzado en Power Query | Microsoft Fabric Community

6 Replies

Syndicate_Admin
Administrator
4 years ago
¿@jeamps01

Descargar archivo PBIX de ejemplo

Esta consulta lo hace

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lEyNDBVitVBEzJGF7LAUGQJETFC1maOKWSBLmSJKWKmFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Index = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Index", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index.1", 0, 1, Int64.Type), #"Sorted Rows" = Table.Sort(#"Added Index",{{"Date", Order.Ascending}, {"Index", Order.Descending}}), #"Grouped Rows" = Table.Group(Table.Buffer(#"Sorted Rows"), {"Date"}, {{"Count", each _, type table [Date=nullable date, Index=nullable number, Index.1=number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index.2", 1, 1, Int64.Type)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Index", "Index.1", "Index.2"}, {"Index", "Index.1", "Index.2"}), #"Sorted Rows1" = Table.Sort(#"Expanded Custom",{{"Date", Order.Ascending}, {"Index.1", Order.Ascending}}), #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows1",{"Index.1"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Index.2", "Ranking"}}) in #"Renamed Columns"

Saludos

Phil
- Syndicate_Admin
  Administrator
  4 years ago
  Hola Felipe
  
  ¡Muchas gracias por la respuesta! Intenté parchear esa consulta en mi fuente original y no obtuve ningún error de sintaxis, así que eso es bueno. Sin embargo, una vez que intentó ejecutar la consulta, obtengo un error que indica:
  
  Expresión.Error: No se encontró la columna 'Index.2' de la tabla.
  Detalles:
  Índice.2
  
  Aquí está la consulta después de que el espacio es su consulta
  
  let Source = SharePoint.Files("XXX", [ApiVersion = 15]), #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "XXX")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", let latest = List.Max(#"Filtered Rows"[Date created]) in each [Date created] = latest), #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[ ¿Oculto]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (6)", each #"Transform File (6)"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (6)"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (6)", Table.ColumnNames(#"Transform File (6)"(#"Archivo de muestra (6)"))), #"Tipo cambiado" = Table.TransformColumnTypes(#"Columna de tabla expandida1",{{"Source.Name", escriba texto}, {"Census#", Int64.Type}, {"DateTY", escriba texto}, {"PropSupTY", Int64.Type}, {"PropDemTY", Int64.Type}, {"PropRevTY", Int64.Type}, {"CompSupTY", Int64.Type}, {"CompDemTY", Int64.Type}, {"CompRevTY", Int64.Type}, {"OccRankTY", type text}, {"ADRRankTY", type text}, {"RevParRankTY", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name "}), #"Columnas reordenadas" = Table.ReorderColumns(#"Columnas eliminadas",{"DateTY", "Census#", "PropSupTY", "PropDemTY", "PropRevTY", "CompSupTY", "CompDemTY", "CompRevTY", "OccRankTY", "ADRRankTY", "RevParRankTY"}), #"Inserted Suffix" = Table.AddColumn(#"Reordered Columns", "Suffix", cada [DateTY] & "01", tipo text), #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Suffix",{{"Suffix", type date}}), #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type1",{"Suffix", "DateTY ", "Census#", "PropSupTY", "PropDemTY", "PropRevTY", "CompSupTY", "CompDemTY", "CompRevTY", "OccRankTY", "ADRRankTY", "RevParRankTY"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"Suffix", "Date"}}), #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"DateTY"}), #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Census#", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type2", "Index", each ([PropRevTY]/[PropSupTY])/([CompRevTY]/[CompSupTY])), #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"Index", type number}}),
  
  #"Changed Type4" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Index", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index.1", 0, 1, Int64.Type), #"Sorted Rows" = Table.Sort(#"Added Index",{{"Date", Order.Ascending}, {"Index", Order.Descending}}), #"Grouped Rows" = Table.Group(Table.Buffer(#"Sorted Rows"), {"Date"}, {{"Count", each _, type table [Date=nullable date, Index=nullable number, Index.1=number]}}), #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index.2", 1, 1, Int64.Type)), #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Count"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Index", "Index.1", "Index.2"}, {"Index", "Index.1", "Index.2"}), #"Sorted Rows1" = Table.Sort(#"Expanded Custom",{{"Date", Order.Ascending}, {"Index.1", Order.Ascending}}), #"Removed Columns3" = Table.RemoveColumns(#"Sorted Rows1",{"Index.1"}), #"Renamed Columns2" = Table.RenameColumns(#"Columnas eliminadas1",{{"Index.2", "Ranking"}}) en #"Columnas renombradas2"
- Syndicate_Admin
  Administrator
  4 years ago
  Hola Felipe
  
  ¡Muchas gracias por la respuesta! Intenté parchear esa consulta en mi fuente original y no obtuve ningún error de sintaxis, así que eso es bueno. Sin embargo, una vez que intentó ejecutar la consulta, obtengo un error que indica:
  
  Expresión.Error: No se encontró la columna 'Index.2' de la tabla.
  Detalles:
  Índice.2
  
  Aquí está la consulta después de que el espacio es su consulta
  
  let Source = SharePoint.Files("XXX", [ApiVersion = 15]), #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "XXX")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", let latest = List.Max(#"Filtered Rows"[Date created]) in each [Date created] = latest), #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (6)", each #"Transform File (6)"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (6)"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (6)", Table.ColumnNames(#"Transform File (6)"(#"Sample File (6)"))), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Census#", Int64.Type}, {"DateTY", type text}, {"PropSupTY", Int64.Type}, {"PropDemTY", Int64.Type}, {"PropRevTY", Int64.Type}, {"CompSupTY", Int64.Type}, {"CompDemTY", Int64.Type}, {"CompRevTY", Int64.Type}, {"OccRankTY", type text}, {"ADRRankTY", type text}, {"RevParRankTY", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"DateTY", "Census#", "PropSupTY", "PropDemTY", "PropRevTY", "CompSupTY", "CompDemTY", "CompRevTY", "OccRankTY", "ADRRankTY", "RevParRankTY"}), #"Inserted Suffix" = Table.AddColumn(#"Reordered Columns", "Suffix", each [DateTY] & "01", type text), #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Suffix",{{"Suffix", type date}}), #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type1",{"Suffix", "DateTY", "Census#", "PropSupTY", "PropDemTY", "PropRevTY", "CompSupTY", "CompDemTY", "CompRevTY", "OccRankTY", "ADRRankTY", "RevParRankTY"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"Suffix", "Date"}}), #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"DateTY"}), #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Census#", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type2", "Index", each ([PropRevTY]/[PropSupTY])/([CompRevTY]/[CompSupTY])), #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"Index", type number}}), #"Changed Type4" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Index", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index.1", 0, 1, Int64.Type), #"Sorted Rows" = Table.Sort(#"Added Index",{{"Date", Order.Ascending}, {"Index", Order.Descending}}), #"Grouped Rows" = Table.Group(Table.Buffer(#"Sorted Rows"), {"Date"}, {{"Count", each _, type table [Date=nullable date, Index=nullable number, Index.1=number]}}), #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index.2", 1, 1, Int64.Type)), #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Count"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Index", "Index.1", "Index.2"}, {"Index", "Index.1", "Index.2"}), #"Sorted Rows1" = Table.Sort(#"Expanded Custom",{{"Date", Order.Ascending}, {"Index.1", Order.Ascending}}), #"Removed Columns3" = Table.RemoveColumns(#"Sorted Rows1",{"Index.1"}), #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns1",{{"Index.2", "Ranking"}}) in #"Renamed Columns2"
  - Syndicate_Admin
    Administrator
    4 years ago
    Hay @jeamps01
    
    ¿Puede enviarme los datos que está utilizando y una captura de pantalla de la tabla en Power Query antes de agregar mis pasos de consulta?
    
    Mi consulta utiliza nombres de columna específicos de las columnas de los datos ficticios que configuré. La consulta utiliza nombres de columna que se encuentran en los datos.
    
    Necesita replicar mis pasos para llevar a cabo las transformaciones en sus datos / columnas. El error que está recibiendo es porque mis datos ficticios tienen una columna llamada Index.2, la suya no.
    
    Si puedo ver qué datos y nombres de columna tiene, puedo intentar modificar mi código para que coincida con los nombres de sus columnas.
    
    Saludos
    
    Phil

Syndicate_Admin

Administrator

4 years ago

Hay @jeamps01 ,

Solo necesita dos pasos:

paso1, agrupar y agregar inex:

= Table.Group(Source, {"Date"}, {{"Partition", each Table.AddIndexColumn(_, "Ranking",1,1), type table}})

Paso 2, expandir tabla:

= Table.ExpandTableColumn(Partition, "Partition", {"Index", "Ranking"}, {"Index", "Ranking"})

Consulta de todo el poder:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lEyNDBVitVBEzJGF7LAUGQJETFC1maOKWSBLmSJKWKmFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", type number}}),
    Partition = Table.Group(Source, {"Date"}, {{"Partition", each Table.AddIndexColumn(_, "Ranking",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index", "Ranking"}, {"Index", "Ranking"})
in
        #"Expanded Partition"

Resultado de salida:

De acuerdo con usted proporcionó, cambie a lo siguiente:

let
    Source = SharePoint.Files("XXX", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "XXX")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", let latest = List.Max(#"Filtered Rows"[Date created]) in each [Date created] = latest),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (6)", each #"Transform File (6)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (6)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (6)", Table.ColumnNames(#"Transform File (6)"(#"Sample File (6)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Census#", Int64.Type}, {"DateTY", type text}, {"PropSupTY", Int64.Type}, {"PropDemTY", Int64.Type}, {"PropRevTY", Int64.Type}, {"CompSupTY", Int64.Type}, {"CompDemTY", Int64.Type}, {"CompRevTY", Int64.Type}, {"OccRankTY", type text}, {"ADRRankTY", type text}, {"RevParRankTY", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"DateTY", "Census#", "PropSupTY", "PropDemTY", "PropRevTY", "CompSupTY", "CompDemTY", "CompRevTY", "OccRankTY", "ADRRankTY", "RevParRankTY"}),
    #"Inserted Suffix" = Table.AddColumn(#"Reordered Columns", "Suffix", each [DateTY] & "01", type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Suffix",{{"Suffix", type date}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type1",{"Suffix", "DateTY", "Census#", "PropSupTY", "PropDemTY", "PropRevTY", "CompSupTY", "CompDemTY", "CompRevTY", "OccRankTY", "ADRRankTY", "RevParRankTY"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"Suffix", "Date"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"DateTY"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Census#", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Index", each ([PropRevTY]/[PropSupTY])/([CompRevTY]/[CompSupTY])),



    #"Changed Type3" = Table.TransformColumnTypes(Source,{{"Index", type number}}),
    Partition = Table.Group(Source, {"Date"}, {{"Partition", each Table.AddIndexColumn(_, "Ranking",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index", "Ranking"}, {"Index", "Ranking"})
in
        #"Expanded Partition"

Y puede referirse a:

https://www.myonlinetraininghub.com/dense-ranking-in-power-query

¿Respondí a su pregunta? ¡Marca mi post como solución!

Saludos

Lucien

Advanced Rank in Power Query.pbix25 KB

Syndicate_Admin
Administrator
3 years ago
Hola a todos,

¿Hay alguna manera de lograr lo mismo pero solo con la función table.addrankcolumn en lugar de usar Agrupar por y Columna de índice?

La razón por la que pregunto es porque si hay un empate, debería obtener la misma posición para los valores con un empate. La columna de índice de la consulta de energía no me dará este resultado.

Por ejemplo, a continuación, las dos primeras filas de líneas deben obtener la misma clasificación basada en el índice:
Fecha Índice Clasificación
1/1/2021 105 1
1/1/2021 105 1
1/1/2021 85 3
1/1/2021 95 2
2/1/2021 107 2
2/1/2021 108 1

¿Alguien sabe cómo hacer esto?
Gracias

Forum Discussion

Rango avanzado en Power Query

6 Replies

Related content

Parámetro hipotético con rango dinámico

Filtros avanzados

filtrado avanzado

cómo aprender DAX avanzado

Desglose avanzado.

Fecha	Índice	Clasificación
1/1/2021	105	1
1/1/2021	103	2
1/1/2021	85	4
1/1/2021	95	3
2/1/2021	107	2
2/1/2021	108	1
2/1/2021	98	3
2/1/2021	96	4