Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-21-2023, 06:35 PM
ScottyBee75 ScottyBee75 is offline Transform Repeating Rows into Column Headings Windows 10 Transform Repeating Rows into Column Headings Office 2019
Novice
Transform Repeating Rows into Column Headings
 
Join Date: Nov 2023
Posts: 3
ScottyBee75 is on a distinguished road
Default Transform Repeating Rows into Column Headings


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
Attached Files
File Type: xlsx Transform Columns.xlsx (13.3 KB, 8 views)
Reply With Quote
  #2  
Old 11-21-2023, 07:15 PM
Alansidman's Avatar
Alansidman Alansidman is offline Transform Repeating Rows into Column Headings Windows 11 Transform Repeating Rows into Column Headings Office 2021
עַם יִשְׂרָאֵל חַי
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 78
Alansidman will become famous soon enoughAlansidman will become famous soon enough
Default

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"
Attached Files
File Type: xlsx Transform Columns.xlsx (21.9 KB, 2 views)
__________________
Alan עַם יִשְׂרָאֵל חַי
Reply With Quote
  #3  
Old 11-22-2023, 02:49 PM
ScottyBee75 ScottyBee75 is offline Transform Repeating Rows into Column Headings Windows 10 Transform Repeating Rows into Column Headings Office 2019
Novice
Transform Repeating Rows into Column Headings
 
Join Date: Nov 2023
Posts: 3
ScottyBee75 is on a distinguished road
Default Solution worked great!

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
Reply With Quote
  #4  
Old 11-22-2023, 04:36 PM
Alansidman's Avatar
Alansidman Alansidman is offline Transform Repeating Rows into Column Headings Windows 11 Transform Repeating Rows into Column Headings Office 2021
עַם יִשְׂרָאֵל חַי
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 78
Alansidman will become famous soon enoughAlansidman will become famous soon enough
Default

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.
__________________
Alan עַם יִשְׂרָאֵל חַי
Reply With Quote
  #5  
Old 11-22-2023, 04:38 PM
p45cal's Avatar
p45cal p45cal is offline Transform Repeating Rows into Column Headings Windows 10 Transform Repeating Rows into Column Headings Office 2021
Expert
 
Join Date: Apr 2014
Posts: 855
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

deleted - links started to appear!
Reply With Quote
  #6  
Old 11-22-2023, 04:39 PM
Alansidman's Avatar
Alansidman Alansidman is offline Transform Repeating Rows into Column Headings Windows 11 Transform Repeating Rows into Column Headings Office 2021
עַם יִשְׂרָאֵל חַי
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 78
Alansidman will become famous soon enoughAlansidman will become famous soon enough
Default

Jumped a minute to soon. Fixed once I realized the links did not come over.
__________________
Alan עַם יִשְׂרָאֵל חַי
Reply With Quote
  #7  
Old 11-22-2023, 05:22 PM
zpy2 zpy2 is offline Transform Repeating Rows into Column Headings Windows 10 Transform Repeating Rows into Column Headings Office 2013
Novice
 
Join Date: Sep 2023
Location: China
Posts: 28
zpy2 is on a distinguished road
Default

【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
Reply With Quote
  #8  
Old 01-19-2024, 03:29 PM
ScottyBee75 ScottyBee75 is offline Transform Repeating Rows into Column Headings Windows 10 Transform Repeating Rows into Column Headings Office 2019
Novice
Transform Repeating Rows into Column Headings
 
Join Date: Nov 2023
Posts: 3
ScottyBee75 is on a distinguished road
Default

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

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Transform Repeating Rows into Column Headings Repeating Header Rows disappeared! macgregorga Word Tables 1 01-09-2020 09:51 PM
Transform Repeating Rows into Column Headings repeating two rows (including merge) as table heading eNGiNe Word 4 10-18-2017 10:57 PM
Transform Repeating Rows into Column Headings Repeating Section (Need Multiple values from different excel rows into one document) NotSaying Mail Merge 3 09-27-2016 02:08 PM
Transform Repeating Rows into Column Headings 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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:26 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft