主页 / coding / 问题 / 78563583
Accepted
Seyma Kalay
Asked: 2024-06-01 19:08:37 +0800 CST

使用权力查询在组中排名

  • 772

我想要groupby and rank样本数据集。我在Factor and YearNum使用数据集进行排名之前对数据集进行了分组 = Table.AddRankColumn(#"previousStep","RankbyYear_WithInTheFactorLevel",{"YearNum",Order.Descending})，但似乎它无法识别电源查询中的上一步。有办法解决这个问题吗？提前谢谢了。

样本数据集

   Factor  YearNum  Col1   Col2 ....
    A       2024
    A       2024
    A       2025
    B       2024
    B       2023

预期答案

Factor  YearNum  Col1  Col2 .... RankbyYear_WithInTheFactorLevel 
A       2024                          2
A       2024                          2
A       2025                          1
B       2024                          1 
B       2023                          2
excel

1 个回答

  1. Best Answer

    在此输入图像描述

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Factor", type text}, {"YearNum", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Factor"}, {{"All", each _, type table [Factor=nullable text, YearNum=nullable number]}}),
    Custom1 = Table.TransformColumns( #"Grouped Rows", {{"All", each  Table.AddRankColumn( _, "rank", {{"YearNum", Order.Descending}} )}}),
    #"Expanded All" = Table.ExpandTableColumn(Custom1, "All", {"YearNum", "rank"}, {"YearNum", "rank"})
in
    #"Expanded All"
    • 3

相关问题