Visuals from pivot table of imported data

[JohnnyK]

Sample data as below.

Name	Team	Score	Credit
Emma	Finance	188	49
Liam	Operation	326	18
Noah	Running	378	10
Ava	Medical	144	34
Oliver	Finance	269	10
Sophia	Running	159	48
William	Operation	496	48

I want to build visuals based on its pivot table (Excel concept), like:

What would be the convenient way in Power BI, that I can produce PivotTable alike from the imported data, and save it as a data table, before starting to build visuals?

Thank you.

[v-yangliu-msft · Accepted solution]

Hi  @JohnnyK  ,

1. When Table1 is the original table, open power query and copy Table1 to form Table2

2. According to your own needs, in Transform, look for Pivot Column for Pivot to form a pivot table Table2

3. Select Close&Aply to visualize in Power BI Desktop

Best Regards,

Liu Yang

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

[JohnnyK]

@v-yangliu-msftthank you for this new knowledge to me!

[JohnnyK]

@amitchandakthank you for your reply. Sorry for not asked the question in full - I meant, then how I can save the pitvot table as a data table, and use the saved pitvot table (as a data table) for visualization.

[amitchandak]

@JohnnyK , In power Query you have option to pivot the table 

https://radacad.com/pivot-and-unpivot-with-power-bi

Hope that can help. You might have to change aggregation if needed. 

Once pivot is create, right click on table and open the Adnace editor, and check for List.Max or List.count or Table.Max or Table.Count and change that to .Sum or .Count if needed 

[amitchandak]

@JohnnyK , You can create table or matrix visual with sum and count of columns.

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual

You can give default summarization in columns tools, if needed or right-click on the column in the visual pane and choose summarization