Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ytulpar
New Member

Transform data to create matrix table

My source data looks like this:

Full NameInternal Work Experience 1:Job TitleInternal Work Experience 1:FromInternal Work Experience 1:ToInternal Work Experience 1:DepartmentInternal Work Experience 1:High Level AccountabilitiesInternal Work Experience 2:Job TitleInternal Work Experience 2:FromInternal Work Experience 2:ToInternal Work Experience 2:DepartmentInternal Work Experience 2:High Level AccountabilitiesExternal Work Experience 1:Job TitleExternal Work Experience 1:Company NameExternal Work Experience 1:FromExternal Work Experience 1:ToExternal Work Experience 1:High Level AccountabilitiesExternal Work Experience 2:Job TitleExternal Work Experience 2:Company NameExternal Work Experience 2:FromExternal Work Experience 2:ToExternal Work Experience 2:High Level AccountabilitiesEducation 1:InstitutionEducation 1:Degree/Diploma ObtainedEducation 1:Program of StudyEducation 1:FromEducation 1:ToEducation 2:InstitutionEducation 2:Degree/Diploma ObtainedEducation 2:Program of StudyEducation 2:FromEducation 2:To
Alex QuintoReceptionist2012-01-012014-02-15HR Executive Assistant2014-02-282016-08-01Directorial Review SpokespersonG&H2017-01-012018-09-30 Fry CookAmrose Foods2020-01-302020-02-20 NovaTech InstituteMaster'sBusiness Administration2011-09-012015-06-20Devry CulinaryFrying and GrillingCooking Education2013-02-072013-11-12
Gregory AlsmanPeacekeeper2023-03-152023-04-02Protection ServicesHelped support building security staffMaintenance Officer2014-02-282016-08-01SCUBACleaned and organized tanksProtection ManagerRoadhouse Sales2017-01-012018-09-30Supported staff of 26 security guardsDirector of OperationsRoadhouse Sales2020-01-302020-02-20 Aurora UniversityBachelor of Science (B.S.)Quantum Computing and Information Science2023-01-152023-02-05Stellar UniversityHigh School DiplomaComputer Science and Engineering2023-03-152023-04-02
Zephyr ThornfieldVirtual Reality Experience Designer2023-06-102023-07-05Finance and AccountingDesign and oversee the construction of next-generation transportation systems for high-speed travel.2023-01-152023-02-05Research and Development           NovaTech InstituteMaster of Business Administration (MBA)Sustainable Urban Planning and Development2023-04-102023-05-20QuantumTech InstituteAssociate's Degree DiplomaEnvironmental Science and Sustainability2023-06-102023-07-05
Seraphina WrenwoodDrone Traffic Controller2023-09-012023-09-15Operations and Logistics Holographic Interface Developer2023-04-102023-05-20Human ResourcesCreate and curate engaging content, analyze performance metrics, and grow online communities to enhance brand presence.Sustainable Energy EngineerQuantum Innovations Inc.2023-07-032023-07-15 Artificial Intelligence Ethics OfficerInfinity Innovations Group2023-03-152023-04-02 Evergreen CollegeDoctor of Medicine (M.D.)Exoplanetary Biology and Astrobiology2023-07-032023-07-15TerraNova CollegeBachelor's Degree DiplomaInternational Business and Economics2023-09-012023-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 TitleCompany NameFromToDepartmentHigh Level AccountabilitiesInstitutionDegree/Diploma ObtainedProgram of StudyFromTo
Internal Work Experience 1Receptionist 2012-01-012014-02-15HR      
Internal Work Experience 2Executive Assistant2014-02-282016-08-01Directorial Review     
External Work Experience 1SpokespersonG&H2017-01-012018-09-30       
External Work Experience 2Fry CookAmrose Foods2020-01-302020-02-20       
Education 1      NovaTech InstituteMaster'sBusiness Administration2011-09-012015-06-20
Education 2      Devry CulinaryFrying and GrillingCooking Education2013-02-072013-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

1 ACCEPTED SOLUTION
slorin
Super User
Super User

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 

View solution in original post

1 REPLY 1
slorin
Super User
Super User

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 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors