Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
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's avatar
    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"

    ranking.png

    Saludos

    Phil

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
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:

vluwangmsft_0-1637306307770.png

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