In what order occurs catalog - Query Editor

[skopcak]

In what order occurs catalog? Here I present what I needed to make

Date	Katalog	result
2017-02-05 10:45:00	10000	1
2017-02-05 23:16:00	10000	2
2017-02-06 23:06:00	50000	1
2017-02-07 17:06:00	50001	1
2017-02-07 17:06:00	8000	1
2017-02-08 14:51:00	400	1
2017-02-09 11:00:00	10000	3
2017-02-09 11:00:00	50000	2

The number 10000 in the column Occurs as one in the sequence

If it meets another number 10000 will already be in the order of 2

thank you for answer

[MarcelBeug]

You can use this code:

    Indexed = Table.AddIndexColumn(PreviousStep, "Index", 0, 1),
    ResultAdded = Table.AddColumn(Indexed, "Result", (earlier) => List.Count(List.Select(List.Range(Indexed[Katalog],0,1+earlier[Index]),each _ =earlier[Katalog])))

[skopcak]

OK, thank you helped. Please do not know how to accelerate the performance of Excel, after this operation takes too long to load, then the next command

[MarcelBeug · Accepted solution]

Oops....

Below alternative code that looks more complicated, but in a test with 10,000 rows it completed in a few seconds while the original solution ran for a few minutes...

Remark: contrary to the previous solution, these are all standard UI steps (except that I renamed every step), with only rather basic formulas entered in the 2 "AddColumn" steps. 

    OriginalSort = Table.AddIndexColumn(PreviousStep, "OriginalSort", 1, 1),
    SortedOnKatalog = Table.Sort(OriginalSort,{{"Katalog", Order.Ascending}, {"OriginalSort", Order.Ascending}}),
    Index0Added = Table.AddIndexColumn(SortedOnKatalog, "Index", 0, 1),
    Index1Added = Table.AddIndexColumn(Index0Added, "Index.1", 1, 1),
    Merged = Table.NestedJoin(Index1Added,{"Index"},Index1Added,{"Index.1"},"Prev",JoinKind.LeftOuter),
    PrevKatalog = Table.ExpandTableColumn(Merged, "Prev", {"Katalog"}, {"Prev.Katalog"}),
    IndicesRemoved = Table.RemoveColumns(PrevKatalog,{"Index", "Index.1"}),
    NewIndex0Added = Table.AddIndexColumn(IndicesRemoved, "Index", 0, 1),
    StartIndex = Table.AddColumn(NewIndex0Added, "StartIndex", each if [Katalog] <> [Prev.Katalog] or [Prev.Katalog] = null then [Index] else null),
    FilledDown = Table.FillDown(StartIndex,{"StartIndex"}),
    ResultAdded = Table.AddColumn(FilledDown, "Result", each 1 + [Index] - [StartIndex]),
    SortedOriginal = Table.Sort(ResultAdded,{{"OriginalSort", Order.Ascending}}),
    ColumnsRemoved = Table.RemoveColumns(SortedOriginal,{"OriginalSort", "Prev.Katalog", "Index", "StartIndex"})

[skopcak]

OK, thank you working fine, you knew this next adjustment?

Table 1

Date	Katalog	piece	result
2017-02-05 10:45:00	10000	1	1
2017-02-05 23:16:00	10000	4	2
2017-02-06 23:06:00	50000	1	1
2017-02-07 17:06:00	50001	2	1
2017-02-07 17:06:00	8000	1	1
2017-02-08 14:51:00	400	3	1
2017-02-09 11:00:00	10000	1	3
2017-02-09 11:00:00	50000	1	2

Table 2

Katalog	Stock	Result(1) – (if Table1[Katalog] = Table2[Katalog] and Table1[result1] = 1 Then Table1[piece])	Result(2) – (if Table1[Katalog] = Table2[Katalog] and Table1[result1] = 2 Then Table1[piece])	Result(3) – (if Table1[Katalog] = Table2[Katalog] and Table1[result1] = 2 Then Table1[piece])	Result(4) – (if Table1[Katalog] = Table2[Katalog] and Table1[result1] = 2 Then Table1[piece])
400	5	3
8000	2	1
10000	1	1	4	1
50000	1	1	1
50001	1	2

[MarcelBeug]

No, I didn't know and I don't understand either, but it looks like a pivot table.