none
NUMBER.IF in Power BI RRS feed

  • Question

  • Hi. I´m trying to find out how to write a formula in Power BI that correspends to formula NUMBER.IF in Excel. 

    I have a table with these two columns. The name "Adam" appears 3 time in the 1st column. In the cell to next to each "Adam" in the second column I want the number 3. The Name "Eve" appears 2 times. The name #Sam 1 time and so on...

    Name Number of times in col.  "Name"
    Adam  3
    Eve 2
    Eve 2
    Adam 3
    Sam 1
    Ken 1
    Mike 1
    Adam 3
    George 1


    

    Thursday, September 21, 2017 11:31 AM

Answers

  • You can add the following steps (in bold) to your M script:

    let
    ....
    PreviousStepName = ....,
    NameList = List.Buffer(PreviousStepName[Name]),
    AddedCustom = Table.AddColumn(PreviousStepName, "Count", each List.Count(List.PositionOf(NameList, [Name], Occurrence.All)))
    in
    AddedCustom



    Thursday, September 21, 2017 3:05 PM

All replies

  • You can add the following steps (in bold) to your M script:

    let
    ....
    PreviousStepName = ....,
    NameList = List.Buffer(PreviousStepName[Name]),
    AddedCustom = Table.AddColumn(PreviousStepName, "Count", each List.Count(List.PositionOf(NameList, [Name], Occurrence.All)))
    in
    AddedCustom



    Thursday, September 21, 2017 3:05 PM
  • Hi Colin,

    do you know if this is faster than the Table.Group-method (List.Count & All)?


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Sunday, September 24, 2017 6:01 AM
    Moderator
  • Hi Imke,

    I did wonder about the potential performance impact of the method I suggested vs table group, but never really tested the difference. My thought was that, well, if the poster complained about the performance, only then I would suggest the table group option, as there are more steps to consider, e.g.:

    1) Before grouping, you have to create an index column for sorting
    2) Then group by name
    3) Then add a custom column for the tables that aggregates the total rows
    4) Then expand the tables
    5) Then re-sort the rows to the original positions based on the index column created in step 1
    6) Then remove the index column

    Sunday, September 24, 2017 6:20 PM
  • Thanks Colin, didn't get the sorting aspect, so your version is much shorter.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Tuesday, September 26, 2017 10:43 AM
    Moderator