Tip to transform data using a similar method to COUNT on Excel

[Anonymous]

Hey there,

I'm rather new to PowerBi hence wanted to get some insights.

I have gotten a dataset where i'm trying to count the number of times a certain keyword appears and transform it into another table. However as it is not as simple as Excel, I was wondering if anyone could advice?

I tried to do a quick illustration using Excel

[Payeras_BI]

Hi @Anonymous ,

I would always go for PQ for these types of transformations, but you still could manually unpivot your table with DAX.

UNPIVOTED TABLE = 
UNION (
    SELECTCOLUMNS (
        'Table',
        "Opportunity Number", 'Table'[Opportunity Number],
        "Value", 'Table'[Criteria 1],
        "Attribute", "Criteria 1"
    ),
    SELECTCOLUMNS (
        'Table',
        "Opportunity Number", 'Table'[Opportunity Number],
        "Value", 'Table'[Criteria 2],
        "Attribute", "Criteria 2"
    ),
    SELECTCOLUMNS (
        'Table',
        "Opportunity Number", 'Table'[Opportunity Number],
        "Value", 'Table'[Criteria 3],
        "Attribute", "Criteria 3"
    )
)

[Payeras_BI]

Hi @Anonymous ,

Would you consider first to unpivot the table in the PQ editor? This would ease things.

[Anonymous]

Hi @Payeras_BI ,

Thanks for the tip. However, the data that i need to pivot is not from the Raw Data set. I've manipulated the raw data sets using from DAX formulas outside the PQ Editor. Is there a way to do it outside the PQ editor?

Thanks!