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
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
Solved! Go to 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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
61 | |
18 | |
16 | |
13 |