Page 2 of 2 FirstFirst 1 2
Results 11 to 13 of 13

Thread: Count if in Power BI

  1. #11
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    61
    Articles
    0
    Excel Version
    Office 365


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Pinarello,

    I have had similar issues to what you are describing when I have multiple queries in Excel that take large amounts of resources. I find better performance by turning off the background refresh. You may have to save, close, quit Excel, and get back into the workbook prior to seeing the benefit.

    Click image for larger version. 

Name:	QueryProperties1.PNG 
Views:	8 
Size:	66.5 KB 
ID:	9207

    Click image for larger version. 

Name:	QueryProperties2.PNG 
Views:	8 
Size:	39.1 KB 
ID:	9208

    I think I responded to the wrong post. I am not sure how to fix that issue.

    Regards,
    Mike
    Last edited by cyborgski; 2019-06-26 at 11:29 AM.

  2. #12
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    33
    Articles
    0
    Excel Version
    Office 365
    Hi, Mike,

    Thank you very much for your quick answer, which I also tested immediately.

    If I deactivate the refresh in the background, it will only refresh directly and at the bottom there will be no more possibility to cancel the refresh. All I can do is close Excel.

    But then I deactivated the checkbox Refresh when all are refreshed.

    And actually I don't need my ineffective query any more. This only shows me how I have found a solution to the question with my level of knowledge at that time. But if I hadn't tested with a larger amount of data back then, I wouldn't have found out how bad my solution was and wouldn't have been able to find out so much new information.

    Thank you so much for letting me share your knowledge!

    Many greetings,
    Pinarello

  3. #13
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,733
    Articles
    0
    Excel Version
    365
    Old thread I know, just going through these old threads for practice.
    I try to answer the question before looking at other responses, then later look at the others.
    So this is a quite different approach, but actually seems quite fast, especially with the Buffer line included:
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"ColA", type text}}),
        Custom1 = List.Buffer(ChangedType[ColA]),
        AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 1, 1),
        AddedCustom = Table.AddColumn(AddedIndex, "Custom", each List.FirstN(Custom1,[Index])),
        AddedCustom1 = Table.AddColumn(AddedCustom, "Custom.1", each List.Count(List.PositionOf([Custom],[ColA],2))),
        TheEnd = Table.RemoveColumns(AddedCustom1,{"Index", "Custom"})
    in
        TheEnd

Page 2 of 2 FirstFirst 1 2

Posting Permissions

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