Counting Services Separately

[Matt_Mohawk]

Hi,

I have a report that I am trying to calulate a number of services provided in one of our showrooms. The information is being pulled from Smartsheet and I have the multi-select function selected on that column. This means they can select multiple services per row and put in how many times that service was used during that visit. I have an image attached showing the data that I need to track. If possible I need a formula that will notice the names of service and track it based on the column name using the counted column.

Let me know if you need any other information.

[StenbergAleksi]

Hello @Matt_Mohawk ,

You can unpivot table in Power Query, so that there is Value and atribute column. Then you can see what is the filtered attribute from values over 0.

Here is the link to the documentation.

https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f5882...

Hope that helped.

If that solved the issue, please mark the reply as solution 🙂

[Matt_Mohawk]

@StenbergAleksi ,

Would this also be able to tell what information was placed in each column? The thing I need to make sure is that the correct data is being calculated based on the Service provided.

[StenbergAleksi]

Hello @Matt_Mohawk ,

Yes it will, In the atribute column will be the column name and the value will be the column value

[Matt_Mohawk]

@StenbergAleksi ,

So this is the problem that I am running into right now. I have data that have 2 different values combined but deliminated with a comma. When I unpivot it does not separate those values and calculate them based on the counted columns. See below: