Forum Discussion
In what order occurs catalog - Query Editor
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"})
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 |
|
|
|
No, I didn't know and I don't understand either, but it looks like a pivot table.