Distinct Count Colunm in Power Query

[Mendes094]

Hello, 

I need to make a distinct count column in POWER QUERY, example:

I have this colunm 'COD Filial' in Power Query:

the expected result would be colunm 'Distinct Count'

 Anybody can help me, please.

Tks.

[Omid_Motamedise · Accepted solution]

use this code to reach the result presneted in the next image

let
Source = Table.FromColumns({{967,892,892,901,901,901}},{"COD FILIAL"}),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Count(List.Distinct(Table.FirstN(#"Added Index",[Index])[COD FILIAL])))
in
#"Added Custom"

[dufoq3 · Accepted solution]

Hi @Mendes094, another solution:

Output:

let
    Source = Table.FromList({967,892,892,901,901,901}, (x)=> {x}, type table[COD FILIAL=Int64.Type]),
    GroupedRows = Table.Group(Source, {"COD FILIAL"}, {{"All", each _, type table},{"Count", each Table.RowCount(_), Int64.Type}}),
    RemovedOtherColumns = Table.SelectColumns(GroupedRows,{"All", "Count"}),
    ExpandedAll = Table.ExpandTableColumn(RemovedOtherColumns, "All", Table.ColumnNames(Source))
in
    ExpandedAll

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solutions everyone offered, and i want to offer some more information for user to refer to.

hello @Mendes094 

You can add an index column first.

Then add a custom column.

=let a=[Index]
in List.Count(List.Distinct(Table.SelectRows(#"Added Index",each  [Index]<=a)[COD FILIAL]))

Output

Best Regards!

Yolo Zhu

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

[dufoq3 · Accepted solution]

Hi @Mendes094, another solution:

Output:

let
    Source = Table.FromList({967,892,892,901,901,901}, (x)=> {x}, type table[COD FILIAL=Int64.Type]),
    GroupedRows = Table.Group(Source, {"COD FILIAL"}, {{"All", each _, type table},{"Count", each Table.RowCount(_), Int64.Type}}),
    RemovedOtherColumns = Table.SelectColumns(GroupedRows,{"All", "Count"}),
    ExpandedAll = Table.ExpandTableColumn(RemovedOtherColumns, "All", Table.ColumnNames(Source))
in
    ExpandedAll

[VRMENDESadq]

I think you dont understand, in my example I just show to you 6 values in COD FILIAL, but I have more than 200000 values in this colunm, this example is just a part.

I need to do something like add a custom colunm and imput a query in M to count distinct how many times each values appears in colunm COD FILIAL.

Do you can help me? need more examples?

Thanks!

@dufoq3 

[dufoq3]

I think you don't understand 😉

Have you read note below my posts?

[VRMENDESadq]

Hello, here is Mendes094 this is my second account.

In this case I already have the table in Power Query, I just need a code to insert a custom colunm.

For example, this step:

Source = Source = Table.FromList({967,892,892,901,901,901}, (x)=> {x}, type table[COD FILIAL=Int64.Type]),

Is not necessary.

Think that I have a big data and the photo is just a little part of the base.

Do you can help me? 

@dufoq3 

[dufoq3]

That step was necessary for sample data. If you want to use my query on your data - read note below my posts.

[Omid_Motamedise · Accepted solution]

use this code to reach the result presneted in the next image

let
Source = Table.FromColumns({{967,892,892,901,901,901}},{"COD FILIAL"}),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Count(List.Distinct(Table.FirstN(#"Added Index",[Index])[COD FILIAL])))
in
#"Added Custom"

[VRMENDESadq]

Hello, here is Mendes094 this is my second account.

In this case I already have the table in Power Query, I just need a code to insert a custom colunm.

For example, this step:

Source = Table.FromColumns({{967,892,892,901,901,901}},{"COD FILIAL"}),

Is not necessary.

Do you can help me? 

@Omid_Motamedise 

[Omid_Motamedise]

So use this

Table.AddColumn(#"Added Index", "Custom", each List.Count(List.Distinct(Table.FirstN(#"Added Index",[Index])[COD FILIAL])))

[AlienSx]

let
    Source = #table(type table [COD FILIAL = Int64.Type], {{967}, {892}, {892}, {901}, {901}, {901}}),
    f = (lst) => List.Repeat({List.Count(lst)}, List.Count(lst)),
    group = Table.Group(Source, "COD FILIAL", {"x", (x) => f(x[COD FILIAL])}),
    xpand = Table.ExpandListColumn(group, "x")
in
    xpand

[Gabry]

With a line 
AddCountColumn = Table.AddColumn(Source, "Count", each List.Count(List.Select(Source[data], (x) => x = _[data])))

[Gabry]

Hello,

let
// Step 1: Import the data
Source = Table.FromRows({
{1},
{1},
{1},
{2},
{2},
{3},
{4},
{4},
{4},
{4}},
{"data"}
),

// Step 2: Group by 'data' and count the occurrences
GroupedTable = Table.Group(Source, {"data"}, {{"Count", each Table.RowCount(_), Int64.Type}}),

// Step 3: Merge the original table with the grouped table to add the 'Count' column
MergedTable = Table.NestedJoin(Source, {"data"}, GroupedTable, {"data"}, "NewColumn", JoinKind.LeftOuter),

// Step 4: Expand the merged column to get the count
ExpandedTable = Table.ExpandTableColumn(MergedTable, "NewColumn", {"Count"}, {"Count"})
in
ExpandedTable