Results 1 to 10 of 10

Thread: Transform blocks of columns to rows

  1. #1
    Neophyte Shirley Moreman's Avatar
    Join Date
    May 2022
    Posts
    3
    Articles
    0
    Excel Version
    365

    Transform blocks of columns to rows



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I'm fairly new to Power Query so have got stumped by this one. I have a set of data for event schedules that I need to transform form blocks of columns relating to dates into a table with rows for the data in each date, including the date and other record information.

    So I want to go from this:

    Click image for larger version. 

Name:	Before.jpg 
Views:	17 
Size:	30.3 KB 
ID:	10967

    to this:


    Click image for larger version. 

Name:	After.jpg 
Views:	18 
Size:	45.7 KB 
ID:	10968

    I've got as far as using Transpose and Fill down to set up the dates and create a list of these, but I'm stuck then with how to pick up the other data and get it into the format I need. I've done some searching but haven't found anything like this.

    Any help would be much appreciated.
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,491
    Articles
    43
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi Shirley,

    This is a bit tricky, as it involves both a pivot and an unpivot. Have a look at the attached.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Neophyte Shirley Moreman's Avatar
    Join Date
    May 2022
    Posts
    3
    Articles
    0
    Excel Version
    365
    Ken you are a total star! Thank you so much.
    I haven't had a chance to study the query yet, but the output looks like it's just what I need. I will take a look and let you know if I have any questions.

  4. #4
    Neophyte MisterBates's Avatar
    Join Date
    Jun 2022
    Posts
    3
    Articles
    0
    Excel Version
    Office 365 (insider)
    Here's another option - very similar to Ken's but I tried NOT to code the column names in each of the "day" blocks in case those blocks vary.

    @Ken - nice solution! I love the way you used promote headers to make the "Type" column names unique 😊
    Attached Files Attached Files

  5. #5
    Neophyte Shirley Moreman's Avatar
    Join Date
    May 2022
    Posts
    3
    Articles
    0
    Excel Version
    365
    This was a great solution too MisterBates �� I'd tried to merge columns and pivot but kept tying myself in knots!

    Thanks both for taking the time to solve this for me. Happy to close off the thread now.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,491
    Articles
    43
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi Shirely, feel free to ignore this thread, but it may also be of interest...

    MisterBates, I was curious to compare our solutions, particularly since you were adding Index columns and performing merges. So what I did was put both our solutions in one workbook, split the data source into a new query, and built a data set that repeats the "data" section of the raw table 1,000 times. I then ran some speed tests over the solutions with my Monkey Tools TimeSleuth.

    Please be aware that this is not criticism of your solution, but rather genuine academic interest on my side. Having said that, here's a view of the results:
    Click image for larger version. 

Name:	compare.png 
Views:	7 
Size:	12.6 KB 
ID:	10973

    The key takeaways for me here are that - with privacy off - the time isn't massively different. (My method averages ~1.7 sec, yours is ~3.6, but with a bit more variation over several refreshes.) But when privacy checks are active (Power Query's default state,) the differences start to become significant. Even with privacy checks on, the methods I used turn out to have little impact, where your method sees significant perf hits and MUCH more variation in refresh times. My guess is that the issues can be laid at the feet of the Index (which essentially buffers the table, forcing a full load into memory before subsequent processing), followed by the merge operations.

    I've attached a workbook with the side-by-side and the output report. Also, full disclosure, I did tweak mine to remove a Changed Type step (to avoid hard coding some columns), as well as added a step to drop the _x suffixes on the "Type" column, as I missed that the first time. Also, in order to get the file small enough to upload to the forum, I had to add a Parameter called MaxRepeats and reduce the data to 100 repeats. If you want to feel the experience I had, just just into Power Query, change that value to 1000, close and do a full refresh.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Neophyte MisterBates's Avatar
    Join Date
    Jun 2022
    Posts
    3
    Articles
    0
    Excel Version
    Office 365 (insider)
    Wow! ... for me, this is the value of a forum. Being able to compare different solutions to a problem. Being able to see the performance differences (and whether a solution is "variable" in terms of performance). And having access to folk with the expertise (and time) to do this kind of analysis.

    A genuine THANKS from me ��

  8. #8
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    177
    Articles
    0
    Excel Version
    Office 365
    Since, as is well known, many roads lead to Rome, here is another possibility.
    Attached Files Attached Files

  9. #9
    Neophyte MisterBates's Avatar
    Join Date
    Jun 2022
    Posts
    3
    Articles
    0
    Excel Version
    Office 365 (insider)
    Quote Originally Posted by pinarello View Post
    Since, as is well known, many roads lead to Rome, here is another possibility.
    Nice! Many ways to solve the requirement indeed.

    Although I'm not liking the dependency that your solution has on the column names - if those changed, or if an additional column was added in each group (or in just one of the groups) the code would need quite some revision.
    Just my two pence :-)

  10. #10
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    153
    Articles
    0
    Excel Version
    Office 365
    Quote Originally Posted by MisterBates View Post
    Nice! Many ways to solve the requirement indeed.

    Although I'm not liking the dependency that your solution has on the column names - if those changed, or if an additional column was added in each group (or in just one of the groups) the code would need quite some revision.
    Just my two pence :-)
    My thoughts as well, although another slick solution, it is not scalable, per the OP's requirements that there will be more columns.
    Oh... by the way, YOU'RE WELCOME!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •