cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fernandez
Frequent Visitor

Tabla con totales

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:

 

Captura.PNG

 

Agradeceria vuestra ayuda!!! 🙂

1 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)

View solution in original post

4 REPLIES 4
themistoklis
Super User
Super User

Hello @Fernandez 

 

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

Also attached the workspace

 

Matrix.jpg

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!

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)

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors