Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## In what order occurs catalog - Query Editor

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

1 ACCEPTED SOLUTION
Community Champion

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

Specializing in Power Query Formula Language (M)
5 REPLIES 5
Community Champion

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])))```
Specializing in Power Query Formula Language (M)
Helper I

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

Community Champion

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

Specializing in Power Query Formula Language (M)
Helper I

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] = 1Then Table1[piece]) Result(2) – (if Table1[Katalog] = Table2[Katalog] and Table1[result1] = 2Then Table1[piece]) Result(3) – (if Table1[Katalog] = Table2[Katalog] and Table1[result1] = 2Then Table1[piece]) Result(4) – (if Table1[Katalog] = Table2[Katalog] and Table1[result1] = 2Then Table1[piece]) 400 5 3 8000 2 1 10000 1 1 4 1 50000 1 1 1 50001 1 2

Community Champion

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

Specializing in Power Query Formula Language (M)

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors