English 中文(简体)
Powerery / How to abolition ofplicarows under specific conditions hold the first entry?
原标题：PowerQuery / How to remove duplicate rows under specific conditions keeping the first entry?
  • 时间：2023-12-09 22:15:33
  •  标签：
  • powerquery

I have almost the same needs as here, from which my title is based. Here s my list : table screenshot Same as data :

ID Version ModificationDateTime StatutModifié
1 2 08/11/2022 16:20:46 Abandon
1 1 14/12/2020 21:43:03 Abandon
2 2 08/11/2022 16:20:46 Affaire
2 1 18/08/2021 17:08:41 Affaire
3 2 08/11/2022 16:20:46 Affaire
3 1 09/02/2021 19:23:18 Affaire
4 4 25/07/2023 15:22:45 Stand-by/report besoin
4 3 13/01/2023 16:17:08 Stand-by/report besoin
4 2 13/01/2023 16:14:13 Stand-by/report besoin
4 1 14/06/2021 16:29:51 Stand-by/report besoin
5 1 10/11/2020 22:56:41 Abandon
6 2 08/11/2022 16:20:46 Affaire
6 1 28/10/2021 15:16:29 Affaire
7 2 08/11/2022 16:20:46 Perte
7 1 30/04/2021 14:14:15 Perte
8 5 20/06/2023 15:10:14 Abandon
8 4 20/06/2023 15:10:09 Stand-by/report besoin
8 3 13/01/2023 16:17:09 Stand-by/report besoin
8 2 13/01/2023 16:14:15 Stand-by/report besoin
8 1 27/08/2021 18:19:16 Stand-by/report besoin
9 2 08/11/2022 16:20:47 Affaire
9 1 09/02/2021 20:15:30 Affaire
10 2 08/11/2022 16:20:47 Affaire
10 1 01/06/2021 16:56:46 Affaire
11 1 18/08/2021 17:11:58 Perte
12 3 08/11/2022 16:20:47 Abandon
12 2 08/11/2022 16:18:25 Abandon
12 1 18/08/2021 17:08:48 Abandon
13 2 08/11/2022 16:20:47 Perte
13 1 19/11/2020 12:47:15 Perte
14 1 05/06/2021 15:23:26 Perte
15 1 04/05/2021 12:28:46 Perte
16 8 20/06/2023 15:09:34 Abandon
16 7 20/06/2023 15:09:15 Stand-by/report besoin
16 6 13/01/2023 16:19:26 Stand-by/report besoin
16 5 13/01/2023 16:17:10 Stand-by/report besoin
16 4 13/01/2023 16:14:16 Stand-by/report besoin
16 3 08/11/2022 16:20:47 Stand-by/report besoin
16 2 08/11/2022 16:18:25 Stand-by/report besoin
16 1 05/10/2021 19:04:03 Stand-by/report besoin
116 16 21/03/2023 11:12:36 Perte
116 15 21/03/2023 11:12:06 Perte
116 14 13/01/2023 16:19:20 Attente accord client
116 13 13/01/2023 16:16:55 Attente accord client
116 12 13/01/2023 16:14:00 Attente accord client
116 11 21/07/2022 16:16:45 Attente accord client
116 10 21/07/2022 16:16:32 Attente accord client
116 9 01/07/2022 17:16:48 Offre à monter
116 8 10/06/2022 12:54:59 Offre à monter
116 7 10/06/2022 12:51:40 Offre à monter
116 6 10/06/2022 12:51:28 Offre à monter
116 5 10/06/2022 12:50:43 Offre à monter
116 4 10/06/2022 12:50:40 Abandon
116 3 11/05/2022 22:32:19 Abandon
116 2 11/05/2022 22:31:44 Abandon
116 1 15/11/2021 12:13:24 Attente accord client

I m tracking some changes on a SharePoint list used as a CRM. In my case, differencies are on datatypes, condition different and the fact I want to keep the first entry rather than the last one.

I successfully applied Davide Bacci s solution (big thanks to him for this sharing) but it runs (very) slowly (more than half an hour) for my 300 rows. Maybe Table.Buffer could help but I ignore this function. Here s is the result I m expecting :

ID Version ModificationDateTime StatutModifié
1 1 14/12/2020 21:43:03 Abandon
2 1 18/08/2021 17:08:41 Affaire
3 1 09/02/2021 19:23:18 Affaire
4 1 14/06/2021 16:29:51 Stand-by/report besoin
5 1 10/11/2020 22:56:41 Abandon
6 1 28/10/2021 15:16:29 Affaire
7 1 30/04/2021 14:14:15 Perte
8 1 27/08/2021 18:19:16 Stand-by/report besoin
9 1 09/02/2021 20:15:30 Affaire
10 1 01/06/2021 16:56:46 Affaire
11 1 18/08/2021 17:11:58 Perte
12 1 18/08/2021 17:08:48 Abandon
13 1 19/11/2020 12:47:15 Perte
14 1 05/06/2021 15:23:26 Perte
15 1 04/05/2021 12:28:46 Perte
16 6 13/01/2023 16:19:26 Offre à monter
16 3 08/11/2022 16:20:47 Besoin à comprendre
16 1 05/10/2021 19:04:03 Stand-by/report besoin
116 15 21/03/2023 11:12:06 Perte
116 10 21/07/2022 16:16:32 Attente accord client
116 5 10/06/2022 12:50:43 Offre à monter
116 2 11/05/2022 22:31:44 Abandon
116 1 15/11/2021 12:13:24 Attente accord client

(此处的大多数相关数据均为有ID 16 &的物项;116)

Trying Ron Rosenfled solution from previous post, I fail with a datatype error so I m surely missing something. Error happens when comparing content on deleteRows step

我的法典最后一部分是根据先前员额制定的,但未能做到这一点。

    #"Colonnes groupées" = Table.Group(#"Minimal", {"ID"}, {
        {"Filtered", (t)=>
            let
                sorted = Table.Sort(t,{"Version", Order.Descending}),
                shifted = Table.FromColumns(
                    Table.ToColumns(t) & {List.RemoveFirstN(t[Version],1) & {null}},
                    type table[ID=number, Version=number, ModificationDateTime=datetime, StatutModifié=text, ShiftedStatus=text]),
                deleteRows = Table.SelectRows(shifted, each [ShiftedStatus] = null or Text.Contains([ShiftedStatus], [StatutModifié], Comparer.OrdinalIgnoreCase))
            in
                deleteRows, type table[ID=number, Version=number, ModificationDateTime=datetime, StatutModifié=text]}
            }),
    #"Expanded" = Table.ExpandTableColumn(#"Colonnes groupées", "Filtered", {"Version","ModificationDateTime","StatutModifié"})
in
    #"Expanded"

任何帮助/升值(和为不填充的首任职位提供询问)

最佳回答

如果我理解正确的话,则尝试这一法典:

<>>Edit:GroupKind. Local supplemented to Table.Group function

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"ID", Int64.Type}, {"Version", Int64.Type}, {"ModificationDateTime", type datetime}, {"StatutModifié", type text}},"en-150"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "StatutModifié"}, {
        {"Min", (t)=>
        Table.FromRecords(
        {Record.SelectFields(Table.Min(t,"ModificationDateTime"),{"Version","ModificationDateTime"})}),
        type table[Version=Int64.Type, ModificationDateTime=datetime]}}, GroupKind.Local),
    #"Expanded Min" = Table.ExpandTableColumn(#"Grouped Rows", "Min", {"Version", "ModificationDateTime"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Min",{"ID", "Version", "ModificationDateTime", "StatutModifié"})
in
    #"Reordered Columns"

守则没有检查在一定时限内有相同身份的物品,但你没有说明可能是什么,也没有提供显示这些问题的数据样本。

The above code creates a table identical to your expected result.

Results for your data as of 2023-12-10 1903Z
enter image description here

时间：2023-12-10 02:57:17
问题回答

暂无回答


上一篇：
下一篇：


相关问题
PowerQuery Custom Function: column value in set of numbers

I am brand new to PowerQuery. I want to make a custom column with a value of 1 if the column value is in a set of numbers (e.g., 3,6,9,14) and 0 otherwise. Besides a big if statement for all the ...

时间：2023-10-09 03:03:52 回答：0 展示：1
Issue Looping Through an entire list in power query

I am pulling data from the internet right now and formatting it into a readable list using power query, but I am running into an issue. When I split the columns into rows (as I have to change the data ...

时间：2023-06-29 02:59:24 回答：0 展示：1
Power BI Subtotal Measure

I need your help with some Power Bi code As you can see, in the Excel Worksheet i have 2 cells (yellow) that are mainly of subtotals and 2 columns (last 2) that depends of these subtotals, however ...

时间：2023-06-11 03:01:45 回答：0 展示：1
热门标签

友情链接

Allggapp Alljchome-教程家园 mvfinale.com-影视剧情大结局大全