Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Countif in power bi
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,335
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Question Re: Countif in power bi

    Quote Originally Posted by pinarello View Post
    Yes, the original order should be retained at the end
    Are you zuriqi?
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  2. #12
    New Member
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    31
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countif in power bi

    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

  3. #13
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,335
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Countif in power bi

    Quote Originally Posted by pinarello View Post
    But that's how I understood the task.
    But not me, in PBI it doesn't matter too much sort order
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  4. #14
    Board Regular
    Join Date
    Sep 2013
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countif in power bi

    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

  5. #15
    New Member
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    31
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countif in power bi

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •