Forum Discussion

Administrator

4 years ago

Solved

Rango avanzado en Power Query

¡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...

Syndicate_Admin

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

¿@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"

Forum Discussion

Rango avanzado en Power Query

Related content

Parámetro hipotético con rango dinámico

Filtros avanzados

filtrado avanzado

cómo aprender DAX avanzado

Desglose avanzado.