Counter in PQ and last unique entries

[PBI_nick]

Hey guys!

The second one task which I`m try to do, so I really appreciate any help 🙂

So, I should to do counter in power query, which count like that

num	counter
15	1
15	2
21	1
786	1
15	3
66	1
21	2

And after all. Sort by last unique entrie, like that (e.x based on prev table)

num	counter
15	3
21	2
786	1
66	1

Danke!

[FrankAT]

Hi @PBI_nick 

in Power Query you can do it like this:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRVitWBU0aGYMrcwgxZ1MwMLhkLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [num = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"num", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"num", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"num"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Regards FrankAT

[Fowmy]

@PBI_nick 

In Power Query.
Right-click on num column and select GROUP BY,
Select SUM under Operation and select  COUNTER in the column.

________________________

If my answer was helpful, please mark this post as a solution, this will also help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube
LinkedIn

[PBI_nick]

I`ll try one more time.

First step - I should create column with counter (which count every entry as new one, e.x num 25 was find first time and then counter = 1, if we find one more 25, counter should be 2 and so on)

Second step - sort as I said.

And I should do it in the Power Query Editor.

[v-lionel-msft]

Hi @PBI_nick ,

Maybe you can do like this.

1. Add an [Index] column in "Edit Query".

2. Create a calculated column.

__Counter = 
RANKX(
    FILTER( ALL(Sheet2), Sheet2[num] = EARLIER(Sheet2[num]) ),
    [Index], , ASC, Dense
)

3. Create a table visual.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PBI_nick]

Thank you for answer, but as a fact I need to create another one column (counter) in dataset and after that filter nums by max unique. Cause after that I should make some calculation (expected value, variance etc.). Do u have any ideas how can I did it?

[Fowmy]

@PBI_nick 

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[PBI_nick]

Excel file with a part of data will be enough for u?

[Fowmy]

@PBI_nick 

Yes, You can also show the expected result.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[PBI_nick]

Ok, I hope it will be enough. Thank u for help!

google drive: https://drive.google.com/file/d/1AapeL6iULemFHcqSUGtfQsKwZxjiCwS3/view?usp=sharing

[Fowmy · Accepted solution]

@PBI_nick 

You can download the file: HERE

Your data in Power Query looks like below before the transformation:

Expected output:

Paste Below code in a blank Query and check the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7LCQAxCATQXjxnwcnfWkL6byOBAYUseHqMOmuJ4rsDkyRFdrqQHRqhOKBSqosRWkRA6SFKGe/S/EUshGWgIYOC9xXycxhRuBOiL6bsfQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Nums of orders" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Nums of orders", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Nums of orders"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"all", each _, type table [Date=nullable date, Nums of orders=nullable number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Date", each List.Max([all][Date])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"all"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Nums of orders", "Count"})
in
    #"Reordered Columns"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[PBI_nick]

Thak u very much. And the last one ask. If I don`t want make second step (max count of unique nums) how can I do this in PQ editor? 

[Fowmy]

@PBI_nick 

Did understand your question, can you explain with example?

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[amitchandak]

@PBI_nick , you can select num and max of counter

or try like

Measure =
VAR __id = MAX ( 'Table'[num] )
VAR __date = CALCULATE ( MAX( 'Table'[counter] ), ALLSELECTED ( 'Table' ), 'Table'[num] = __id )
CALCULATE ( Min ( 'Table'[counter] ), VALUES ( 'Table'[num ), 'Table'[num] = __id, 'Table'[counter] = __date )

[PBI_nick]

So, I need create another one column here (counter). How can I do this?

And just after that take second step