+ Reply to Thread
Results 1 to 3 of 3

Pivoting data to consolidate number of columns

  1. #1
    Registered User
    Join Date
    05-11-2022
    Location
    Canada
    MS-Off Ver
    365
    Posts
    2

    Pivoting data to consolidate number of columns

    Hi all, I'm new here and looking for some help on an issue I am having with my data. I currently have a matrix with a column of Projects and then columns for each week of the year with the values being the number of hours required for that week. I want to pivot the data so that I have only 3 columns , the Week, Project, and Hours so that I can transform it easier in PowerBI. I have attached a spreadsheet with my current data state as well as the desired format I want but I'm unsure how to create the output.

    Any help on this is much appreciated!
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Pivoting data to consolidate number of columns

    Can be achieved by unpivoting data using Power Query which is called Get and Transform Data and found on the Data Tab. Here is the Mcode

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    05-11-2022
    Location
    Canada
    MS-Off Ver
    365
    Posts
    2

    Re: Pivoting data to consolidate number of columns

    Thank you Alan, this is great! I will definitely look further into the capabilities of Power Query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Consolidate data in multiple columns
    By Mr_Phil in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-15-2020, 12:04 AM
  2. [SOLVED] Consolidate Rows and Columns with Data
    By ionelz in forum Excel General
    Replies: 4
    Last Post: 01-31-2020, 02:27 PM
  3. [SOLVED] Consolidate data from rows to columns
    By ABSTRAKTUS in forum Excel General
    Replies: 2
    Last Post: 02-12-2019, 08:27 AM
  4. [SOLVED] Consolidate data from several columns to one
    By ABSTRAKTUS in forum Excel General
    Replies: 13
    Last Post: 05-04-2017, 09:34 AM
  5. Consolidate data from different columns
    By Moazam Ali in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-28-2016, 03:08 PM
  6. pivoting total count of multiple columns from a table
    By kor123 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-26-2016, 01:11 AM
  7. Pivoting table with grouped columns
    By sirwolfwood in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-10-2015, 03:45 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1