cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
skopcak Regular Visitor
Regular Visitor

In what order occurs catalog - Query Editor

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

 

DateKatalogresult
2017-02-05 10:45:00100001
2017-02-05 23:16:00100002
2017-02-06 23:06:00500001
2017-02-07 17:06:00500011
2017-02-07 17:06:0080001
2017-02-08 14:51:004001
2017-02-09 11:00:00100003
2017-02-09 11:00:00500002

 

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

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: 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"})

 

Specializing in Power Query Formula Language (M)
5 REPLIES 5
MarcelBeug Super Contributor
Super Contributor

Re: In what order occurs catalog - Query Editor

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)
skopcak Regular Visitor
Regular Visitor

Re: In what order occurs catalog - Query Editor

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 Super Contributor
Super Contributor

Re: 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"})

 

Specializing in Power Query Formula Language (M)
skopcak Regular Visitor
Regular Visitor

Re: In what order occurs catalog - Query Editor

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 Super Contributor
Super Contributor

Re: In what order occurs catalog - Query Editor

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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 398 members 3,787 guests
Please welcome our newest community members: