Forum Discussion

peppelino's avatar
peppelino
Frequent Visitor
3 years ago

Count columns where value is not {null, blank, 0}, filtered on column name by condition

Hi, 

 

i have a dynamic number of columns in one table, with metrics calculated for countries.

Each country is concatenated with the metrics:

productUSA minsCAN minsSWE minsDEN minsUSA profitCAN profitSWE profitDEN profit
car1null(blank)0200200200200
bike110null200200200200

 

I need a column for a specific metric, say mins, that would count the number of columns where the value is not null, zero or blank in this scenario.

The current issue is that I do not know what countries will be included in the data when I refresh it.

 

it would be a combination of the below 2 calculations, but I cannot figure out how to add them together.

 

 

 

 

List.Count(List.RemoveNulls(List.Difference(List.Range(Record.FieldValues(_),2), {null, "", " ", 0})))

 

 

 

 

 

 

 

List.Sum(List.Transform(List.Select(Record.FieldNames(_), each Text.EndsWith(_, "mins")), (name) => Record.Field(_, name)))

 

 

 

 

 

Any help is appreciated. 

 

Thanks

Peter

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi peppelino ,

    Is it possible to consider selecting the [product] column, and then unpivot the other columns, before proceeding to the next calculation? This way, even if the column is dynamic, it is still easy to calculate.

    Calculate the results and then pivot back to the original table layout.

     

    Best Regards,
    Gao

    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum