Share via

How to transform in power query

Nicholas Jimenez 20 Reputation points
Jan 29, 2024, 12:14 AM

This is the pretty format. the original is just a disaster.
I have over 100 sheets and am trying to automate as much as possible. User's image

Trying to get this:
User's image

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
2,073 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jiajing Hua-MFST 12,885 Reputation points Microsoft Vendor
    Jan 29, 2024, 7:06 AM

    Hi @Nicholas Jimenez

    I suggest you replace blank values with "0" in Power Query then use "Unpivot Columns" function.

    • Go to Excel > Data group > Get & Transform Data group > Click Get Data > From File > From Excel Workbook, load your data into Power Query.
    • Go to Transform tab > Table group > Use First Row as Headers.
    • Select value columns > right-click on the first row > Replace Values > Enter "null" into Values to find, enter "0" into Replace With.

    User's image

    • Keep selecting these columns > Transform tab > Any Column group > Unpivot Columns (If you do not replace null values, then they will be removed after doing this step, you my replace 0 with null values back later).
    • Rename the headers.
    • Right-click the Date column > Change Type > Date.
    • Go to Add Column tab > From Date & Time group > Date > Day > Name of Day, then rename the header to "Day".

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.