Countif in power bi

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

pinarello

New Member
Joined
Jun 1, 2019
Messages
33
zuriqi last replied on 30 May. But that's how I understood the task.


But I don't care, because when I help, it's primarily to learn for myself. And I also succeeded very well with this thread
 

citizenbh

Board Regular
Joined
Sep 19, 2013
Messages
137
Try this:

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColA", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "ColA", "ColA - Copy"),
    #"Added Index" = Table.AddIndexColumn(#"Duplicated Column", "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"ColA - Copy"}, {{"CountAll", each _, type table [ColA=text, #"ColA - Copy"=text, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Index2", each Table.AddIndexColumn([CountAll], "Index2", 1, 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"CountAll"}),
    #"Expanded Index2" = Table.ExpandTableColumn(#"Removed Columns", "Index2", {"ColA", "Index", "Index2"}, {"ColA", "Index", "ColB"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Index2",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"ColA", "Index"})
in
    #"Removed Columns1"
On my computer it lasts for:

Excelguru.ca solution - about 33 seconds
My solution - 5 seconds
 

pinarello

New Member
Joined
Jun 1, 2019
Messages
33
That's a great solution, too. At the test with 960.000 lines, it took about 13 seconds. Almost identical with the first solution from cyborgski, which takes about 12 seconds on my computer. Only the second solution of cyborgski needs about 40 seconds for this amount of data.


At the test with 120.000 lines all 3 solutions are, from the feeling, equally fast.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,876
Messages
5,471,278
Members
406,752
Latest member
hkyplyr26

This Week's Hot Topics

Top