Transpose and unpivot table

[Rosario]

Hi I can I get help to transpose and unpivot this table please? Thank YOU.

				Profession	Travel Overseas		Children
Centre Code	Locations	Marriages	Divorces	Yes	No	Yes	No	Yes	No
14	Aguascalientes	88	117	79	9	2	86	65	23
480	Baja California	103	143	101	2	0	103	76	27
2358	Baja California Sur	58	121	57	1	0	58	35	23
2489	Campeche	58	94	56	2	0	58	32	26
2494	Chiapas	111	152	110	1	0	111	74	37
2501	Chihuahua	65	124	56	9	0	65	39	26
5001	Ciudad de México	33	50	27	6	0	33	0	33
5125	Coahuila	58	118	57	1	0	58	47	11
5164	Colima	43	69	26	17	0	43	19	24
5302	Durango	111	160	108	3	0	111	43	68
5313	Guanajuato	91	119	86	5	1	90	54	37
5316	Guerrero	113	196	107	6	0	113	71	42
5346	Hidalgo	58	73	58	0	0	58	43	15

I want to have it as below example:

Status	Centre Code	Locations	Value
Marriages	14	Aguascalientes	88
Marriages	480	Baja California	103
Marriages	2358	Baja California Sur	58
Marriages	2489	Campeche	58
Marriages	2494	Chiapas	111
Marriages	2501	Chihuahua	65
Marriages	5001	Ciudad de México	33
Marriages	5125	Coahuila	58
Marriages	5164	Colima	43
Marriages	5302	Durango	111
Marriages	5313	Guanajuato	91
Marriages	5316	Guerrero	113
Marriages	5346	Hidalgo	58
Divorces	14	Aguascalientes	117
Divorces	480	Baja California	143
Divorces	2358	Baja California Sur	121
Divorces	2489	Campeche	94
Divorces	2494	Chiapas	152
Divorces	2501	Chihuahua	124
Divorces	5001	Ciudad de México	50
Divorces	5125	Coahuila	118
Divorces	5164	Colima	69
Divorces	5302	Durango	160
Divorces	5313	Guanajuato	119
Divorces	5316	Guerrero	196
Divorces	5346	Hidalgo	73
Profession-Yes	14	Aguascalientes	79
Profession-Yes	480	Baja California	101
Profession-Yes	2358	Baja California Sur	57
Profession-Yes	2489	Campeche	56
Profession-Yes	2494	Chiapas	110
Profession-Yes	2501	Chihuahua	56
Profession-Yes	5001	Ciudad de México	27
Profession-Yes	5125	Coahuila	57
Profession-Yes	5164	Colima	26
Profession-Yes	5302	Durango	108
Profession-Yes	5313	Guanajuato	86
Profession-Yes	5316	Guerrero	107
Profession-Yes	5346	Hidalgo	58
Profession-No	14	Aguascalientes	9
Profession-No	480	Baja California	2
Profession-No	2358	Baja California Sur	1
Profession-No	2489	Campeche	2
Profession-No	2494	Chiapas	1
Profession-No	2501	Chihuahua	9
Profession-No	5001	Ciudad de México	6
Profession-No	5125	Coahuila	1
Profession-No	5164	Colima	17
Profession-No	5302	Durango	3
Profession-No	5313	Guanajuato	5
Profession-No	5316	Guerrero	6
Profession-No	5346	Hidalgo	0

[Anonymous · Accepted solution]

Hi @Rosario,

You have to just do transformation in Advance query editor mode in power bi.

I have taken your sample data and met your requeriments.

kindly have a look and revert me if any concern.

https://drive.google.com/file/d/1NGhZWSTPVcoFwIMNT-aSHoMt9lW59tTh/view?usp=sharing 

Best Regards,

Ravi

[Anonymous · Accepted solution]

Hi @Rosario,

You have to just do transformation in Advance query editor mode in power bi.

I have taken your sample data and met your requeriments.

kindly have a look and revert me if any concern.

https://drive.google.com/file/d/1NGhZWSTPVcoFwIMNT-aSHoMt9lW59tTh/view?usp=sharing 

Best Regards,

Ravi

[Rosario · Accepted solution]

Thanks @Anonymous, it is exactly as I want it. I will re do it following your steps in edit query. Cheers

Rosario