如何在Microsoft Excel Power查询中找到每个ID的最大值？

Question

Job_ID	Day_id
430	120
430	720
530	120
530	620
620	630
120 630	120
630	620
630	620

应成为：

job_id	day_id
430	720
530	530 620
620	630
620 630	620

只有day_id的行中只有最大值。

我目前正在使用Excel的Power查询，因此不可用Python和R脚本。

Answer 1

为了将重复项保持在最后，您必须摆弄M。

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE2UFDSUTI0MlCK1YFzzaFcU1RZKNcMyjVDlcXONcPCjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"JOB_ID " = _t, DAY_ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"JOB_ID ", Int64.Type}, {"DAY_ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"JOB_ID "}, {{"All", each 

    let a = List.Max(_[DAY_ID]),
    b =  List.Select(_[DAY_ID], each _ =   a)
    in b

    , type list}}),
    #"Expanded All" = Table.ExpandListColumn(#"Grouped Rows", "All")
in
    #"Expanded All"

Answer 2

• 通过job_id
• 组组汇总来通过最大day_id

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table20"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"JOB_ID", Int64.Type}, {"DAY_ID", Int64.Type}}),

//Group by JOB_ID
//  Filter each subtable by maximum DAY_ID
    #"Grouped Rows" = Table.Group(#"Changed Type", 
        {"JOB_ID"}, {
            {"DAY_ID", (t)=>Table.SelectRows(t, each [DAY_ID]=List.Max(t[DAY_ID])), 
                    type table [JOB_ID=Int64.Type, DAY_ID=Int64.Type]}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"JOB_ID"}),
    
    #"Expanded DAY_ID" = Table.ExpandTableColumn(#"Removed Columns", "DAY_ID", {"JOB_ID", "DAY_ID"})
in
    #"Expanded DAY_ID"

Answer 3

您可能需要按JOB_ID进行分组，然后选择汇总最大day_id

编辑：（请参见下文评论）
与原始列表的匹配可以使Job_ID的重复与相同的最大day_id保持