Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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"})
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 |
|
|
|
No, I didn't know and I don't understand either, but it looks like a pivot table.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
85 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
97 | |
71 | |
67 |