一个人如何在不使用枢轴的情况下崩溃多行数据

发布于 2025-02-01 02:06:20 字数 490 浏览 1 评论 0原文

我已经在这个论坛上提出了一个问题，即如何崩溃多行的单元格值，其身份号码识别出散布在表格上的学生的记录。其中一些数据集是从Web导入的数据，Pivot将删除NULLS值。这些零是稍后填写的数据，列日期按降序排序，以便在丢弃先前记录时保留最新记录。就像删除重复项并保留最新记录。 https://ibb.co/zh2rxrz 这就是我想要的： https://ibb.co/98HMCJJJ

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浸婚纱 2025-02-08 02:06:20

看起来您正在尝试根据日期列找到每个ID的最新行，

只需对数据进行分组，

#"Grouped Rows" = Table.Group(#"PriorStepNameHere", {"ID"}, {{"data", each Table.FirstN(Table.Sort(_,{{"Date", Order.Descending}}),1), type table}})

然后在新列的顶部使用箭头，然后扩展额外的列

i.sstatic.net/28xvk.jpg 上图的代码：

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"data", each Table.FirstN(Table.Sort(_,{{"Date", Order.Descending}}),1), type table}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Date", "Something", "Something2", "Something3"}, {"Date", "Something", "Something2", "Something3"})
in  #"Expanded data"

这是一个版本，将“填充”用于除ID和日期以外的所有列的最新日期。图像下面的示例代码

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {
   {"data", each 
        Table.FirstN(
            Table.FillUp(
                Table.Sort(_,{{"Date", Order.Descending}}),
                List.Difference( Table.ColumnNames(_),{"ID","Date"}  )
            )
        ,1)
        , type table}
        }),
    #"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Something1", "Something2", "Something3"}, {"Something1", "Something2", "Something3"})
in #"Expanded data"

It looks you are trying to find the most recent row for each ID, based on the Date column

If so, just group the data

#"Grouped Rows" = Table.Group(#"PriorStepNameHere", {"ID"}, {{"data", each Table.FirstN(Table.Sort(_,{{"Date", Order.Descending}}),1), type table}})

then use arrows on top of the new column and expand the extra columns

sample code for image above:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"data", each Table.FirstN(Table.Sort(_,{{"Date", Order.Descending}}),1), type table}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Date", "Something", "Something2", "Something3"}, {"Date", "Something", "Something2", "Something3"})
in  #"Expanded data"

this is a version that does a "Fill up" into latest date for all columns except ID and Date. Sample code for image below

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {
   {"data", each 
        Table.FirstN(
            Table.FillUp(
                Table.Sort(_,{{"Date", Order.Descending}}),
                List.Difference( Table.ColumnNames(_),{"ID","Date"}  )
            )
        ,1)
        , type table}
        }),
    #"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Something1", "Something2", "Something3"}, {"Something1", "Something2", "Something3"})
in #"Expanded data"