Tabla con totales

[Fernandez]

Buenas, estoy intentando hacer un pivot en una tabla para obtenet un total, pero no lo consigo. Necesitaria vuestra ayuda por que estoy totalmente bloqueado. Os muestro:

Tengo una tabla con la lista de departamentos, usuarios y cursos que ha hecho cada usuario, y necesito transformarla en una tabla que unica mente muestre el departamento y el total de usuarios de ese departamento, algo asi:

Agradeceria vuestra ayuda!!! 🙂

[themistoklis]

Hello @Fernandez 

Create a matrix visual and add the fields in the sections shown in the image below.

Also attached the workspace

[Fernandez]

Hi @themistoklis , first to all thx for your answer. I need the table in the data model, I mean from the transform data option, I would like to create a table with the data grouped by Department(Departamento). , but i can't do it, any idea? thanks again!

[themistoklis · Accepted solution]

Hello @Fernandez 

Attached file shows how you can do it in Power Query.

Following is the Power Query code:

let
Source = Excel.Workbook(File.Contents("C:\...........\Department.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Table",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Departamento", type text}, {"usuario", type text}, {"cursos", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"cursos"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Departamento"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
in
#"Grouped Rows"

You need to delete cursos field first.

Then right click on Departmento column in Power Query and select Group By (Count Distinct Rows)

[Fernandez]

Hi @themistoklis works!!! many many thanks man!!! 🙂