Transform data to create matrix table

[ytulpar]

My source data looks like this:

Full Name

Internal Work Experience 1:Job Title

Internal Work Experience 1:From

Internal Work Experience 1:To

Internal Work Experience 1:Department

Internal Work Experience 1:High Level Accountabilities

Internal Work Experience 2:Job Title

Internal Work Experience 2:From

Internal Work Experience 2:To

Internal Work Experience 2:Department

Internal Work Experience 2:High Level Accountabilities

External Work Experience 1:Job Title

External Work Experience 1:Company Name

External Work Experience 1:From

External Work Experience 1:To

External Work Experience 1:High Level Accountabilities

External Work Experience 2:Job Title

External Work Experience 2:Company Name

External Work Experience 2:From

External Work Experience 2:To

External Work Experience 2:High Level Accountabilities

Education 1:Institution

Education 1:Degree/Diploma Obtained

Education 1:Program of Study

Education 1:From

Education 1:To

Education 2:Institution

Education 2:Degree/Diploma Obtained

Education 2:Program of Study

Education 2:From

Education 2:To

Alex Quinto

Receptionist

2012-01-01

2014-02-15

HR

Executive Assistant

2014-02-28

2016-08-01

Directorial Review

Spokesperson

G&H

2017-01-01

2018-09-30

Fry Cook

Amrose Foods

2020-01-30

2020-02-20

NovaTech Institute

Master's

Business Administration

2011-09-01

2015-06-20

Devry Culinary

Frying and Grilling

Cooking Education

2013-02-07

2013-11-12

Gregory Alsman

Peacekeeper

2023-03-15

2023-04-02

Protection Services

Helped support building security staff

Maintenance Officer

2014-02-28

2016-08-01

SCUBA

Cleaned and organized tanks

Protection Manager

Roadhouse Sales

2017-01-01

2018-09-30

Supported staff of 26 security guards

Director of Operations

Roadhouse Sales

2020-01-30

2020-02-20

Aurora University

Bachelor of Science (B.S.)

Quantum Computing and Information Science

2023-01-15

2023-02-05

Stellar University

High School Diploma

Computer Science and Engineering

2023-03-15

2023-04-02

Zephyr Thornfield

Virtual Reality Experience Designer

2023-06-10

2023-07-05

Finance and Accounting

Design and oversee the construction of next-generation transportation systems for high-speed travel.

2023-01-15

2023-02-05

Research and Development

NovaTech Institute

Master of Business Administration (MBA)

Sustainable Urban Planning and Development

2023-04-10

2023-05-20

QuantumTech Institute

Associate's Degree Diploma

Environmental Science and Sustainability

2023-06-10

2023-07-05

Seraphina Wrenwood

Drone Traffic Controller

2023-09-01

2023-09-15

Operations and Logistics

Holographic Interface Developer

2023-04-10

2023-05-20

Human Resources

Create and curate engaging content, analyze performance metrics, and grow online communities to enhance brand presence.

Sustainable Energy Engineer

Quantum Innovations Inc.

2023-07-03

2023-07-15

Artificial Intelligence Ethics Officer

Infinity Innovations Group

2023-03-15

2023-04-02

Evergreen College

Doctor of Medicine (M.D.)

Exoplanetary Biology and Astrobiology

2023-07-03

2023-07-15

TerraNova College

Bachelor's Degree Diploma

International Business and Economics

2023-09-01

2023-09-15

I want to reorganize my data so I can create a matrix table visualization filtered for each individual that looks like this:

Alex Quinto

	Job Title	Company Name	From	To	Department	High Level Accountabilities	Institution	Degree/Diploma Obtained	Program of Study	From	To
Internal Work Experience 1	Receptionist		2012-01-01	2014-02-15	HR
Internal Work Experience 2	Executive Assistant	2014-02-28	2016-08-01	Directorial Review
External Work Experience 1	Spokesperson	G&H	2017-01-01	2018-09-30
External Work Experience 2	Fry Cook	Amrose Foods	2020-01-30	2020-02-20
Education 1							NovaTech Institute	Master's	Business Administration	2011-09-01	2015-06-20
Education 2							Devry Culinary	Frying and Grilling	Cooking Education	2013-02-07	2013-11-12

I'm not clear on how to reorganize my source data to achieve the visualization outcome I'm looking for. Could someone advise on the best way to do this?

Thank you

[slorin · Accepted solution]

Hi,

Unpivot, Split by ":", Pivot

let
Source = YourSource,
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Full Name"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.2]), "Attribute.2", "Value")
in
#"Pivoted Column"

Stéphane