how to club columns in category

[Anonymous]

Hi,

Is there any way to clubbed multiple columns in the categories to show them in matrix visual.

As like below, Column1 and Column2 in Category A, Column3 and Column4 in Category B and Column5 in Category 😧

Thanks

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, here's my solution.

Sample:

1. In Power Query, select the columns(column1 to column5) then click "Unpivot Columns"

Result:

2. Create a calculated column "Category".

Category =
SWITCH (
    'Table'[Attribute],
    "Column1", "Category A",
    "Column2", "Category A",
    "Column3", "Category B",
    "Column4", "Category B",
    "Column5", "Category C"
)

3. In matrix, put Date in Rows, Category and Attribute in Columns, Value in Values, then click expand.

Result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Ritaf1983 ,

These are simple columns which have no hierarchy. we need to provide them categories and show relevant columns under those categories on matrix column.

Thanks

Thanks

[Ritaf1983]

Hi @Anonymous 
Why is adding a classified column (to the data table) problematic?

[Ritaf1983]

Hi @Anonymous 
If your columns are hierarchies of category and subcategory, just put a hierarchy on columns and open it:

In the other case if there are subcategories of measures you can use text boxes / shapes up to needed columns

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly