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"})
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])))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
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
