Transform Repeating Rows into Column Headings

ScottyBee75 · #1 11-21-2023, 06:35 PM

When using SQL Server, I had a way to transform (Pivot) the unique values in a column and make them column headings. I am trying to figure out how to do this using the Excel Transform feature that is new to Excel 2019. In the attached file, I want to transform the three unique values in the Course column and make them three separate column headings. The corresponding dates would then move into one row for each person making the report more compact.

Any help on how to accomplish this would be much appreciated. Thanks

Alansidman · #2 11-21-2023, 07:15 PM

With Power Query/Get and Transform Data

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Enrolled", type date}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Course]), "Course", "Date Enrolled")
in
    #"Pivoted Column"

ScottyBee75 · #3 11-22-2023, 02:49 PM

Thanks Alansidman as your solution worked out great. I know VBA but this Power Query syntax is foreign to me. Is the code you provided something you learned by doing several Power Queries or is their a library of code snippets somewhere that you can just copy and paste?

Thanks again

Alansidman · #4 11-22-2023, 04:36 PM

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Power Query - How To Paste Code (video) - Excel Solutions

These links should help you to understand and use the Mcode.

p45cal · #5 11-22-2023, 04:38 PM

deleted - links started to appear!

Alansidman · #6 11-22-2023, 04:39 PM

Jumped a minute to soon. Fixed once I realized the links did not come over.

zpy2 · #7 11-22-2023, 05:22 PM

【sql-Transform Repeating Rows into Column Headings-】

online sql

//select * from basic_to_two_dim limit 20;
cli_no_header;
cli_create_two_dim~basic_to_two_dim~f04~f05;
select * from basic_to_two_dim_two_dim

ScottyBee75 · #8 01-19-2024, 03:29 PM

Thanks gentlemen for the great tips and resources. Sorry it took so long for me to reply.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Repeating Header Rows disappeared!	macgregorga	Word Tables	1	01-09-2020 09:51 PM
repeating two rows (including merge) as table heading	eNGiNe	Word	4	10-18-2017 10:57 PM
Repeating Section (Need Multiple values from different excel rows into one document)	NotSaying	Mail Merge	3	09-27-2016 02:08 PM
Repeating or freezing header rows	DLeighC	OneNote	1	09-05-2015 06:49 AM
Repeating table rows in template?	TheWedgie	Word	0	09-11-2010 09:35 PM