Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: How to reuse a transformation process in unpivotting

  1. #1
    Seeker R1C1's Avatar
    Join Date
    May 2020
    Posts
    9
    Articles
    0
    Excel Version
    Microsoft Excel 2016 Pro Plus

    Cool How to reuse a transformation process in unpivotting



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

    Hi Excel Guru,

    I have an unpivotting process with three levels of headings. I know the process of unpivotting but I don't find a way to automate it since I get it everyday. My columns are as follows.

    First merged column - Product Category: three column headers
    Second merged column - Years: three years for each Product Category
    Third merged column - Months: 12 months for each Year
    Two Rows - Countries and Regions, Sales in the intersection.

    I unpivot it using some transposes, fill-downs, merges and unmerges. I get this data daily. But when I have another excel sheet with the same raw data, I don't find a way to follow the same transformation process for my new data. I do not know which data source I need to take in the Power Query's new query. If I save all the raw data in different worksheets in a workbook, PQ is not taking all the sheets in to transformation. If I save as table/range transformation happens only for that table/range.

    So I am confused on what to do, this question is not about the process I think, it is about the data source selection in the new query tab of PQ so that I can do it automatically.

    Advance thanks

    Srinivas

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    Where is the data sourced from?

  3. #3
    Seeker R1C1's Avatar
    Join Date
    May 2020
    Posts
    9
    Articles
    0
    Excel Version
    Microsoft Excel 2016 Pro Plus
    Hello Phillips,

    The data source in my case is Excel workbooks from a folder which will be generated within my organization periodically.

  4. #4
    Seeker R1C1's Avatar
    Join Date
    May 2020
    Posts
    9
    Articles
    0
    Excel Version
    Microsoft Excel 2016 Pro Plus
    Phillips,

    Here is the link for sample data. Thanks.

    https://drive.google.com/file/d/1iA9...ew?usp=sharing

  5. #5
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    Can you post your PQ code as well, and tell us what is going wrong, as I didn't quite get what you said before.

    Is the problem that each day is a different file, and you don't know what it is called beforehand?

  6. #6
    Seeker R1C1's Avatar
    Join Date
    May 2020
    Posts
    9
    Articles
    0
    Excel Version
    Microsoft Excel 2016 Pro Plus
    Phillips,

    I do not have any code. I am still figuring out on how to do this process automatically for multiple workbooks that I receive everyday. I can process one file at a time but not multiple workbooks which are in the same format.

    FYI, the file type is same every day i.e. Excel workbook shared in particular folder.

    If I understand your question properly, "Are asking me to process the single file once and send you the PQ code?"

    thanks
    Srinivas

  7. #7
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    Now you have really confused me. In post #1 you say that you have an unpivotting process,. noiw yuou say you don't have any PQ code. Exactly what help are you after? Do you want some unpivotting code with nested headers, or do you jusrt want to know how to get multiple files from a folder. If the latter, PQ proivides that facility, Get & Transform>New Query>From File>From Folder. It will ask you to browse to the folder, and then it will show you all the files. Double click the double down arrow to the right of the Content header and they will all open appended, then you can do the transposing, filling and unpivotting.

  8. #8
    Seeker R1C1's Avatar
    Join Date
    May 2020
    Posts
    9
    Articles
    0
    Excel Version
    Microsoft Excel 2016 Pro Plus
    Phillips,

    I want to "know the way to do the un-pivotting process for multiple files (which are from a folder) automatically". I know how to un-pivot a single file. When I have multiple files in a folder, and tried to import the same into PQ, at the preview pane I can see and import only the first file. I couldn't file the other files for some reason. That's where the problem. So I thought I can change the source from folder to Excel workbook, so that I can process that in a diff way. Please don't hesitate ask me if it is still not clear.

    thanks

  9. #9
    Seeker R1C1's Avatar
    Join Date
    May 2020
    Posts
    9
    Articles
    0
    Excel Version
    Microsoft Excel 2016 Pro Plus
    Phillips,

    Here is the link to the folder that has the target files. These are what I will receive everyday and I need to unpivot them every time. Sometimes there will be 10 files like these. But all are uniform in layout, not a single alphabet miss the queue. We can easily unpivot a single file, but for multiple files, the nowmal unpivotting process won't work out.

    So please download the two files and do the un-pivoting process taking the folder as the source. For me it did not workout. Please check.

    https://drive.google.com/drive/folde...fx?usp=sharing

    Thanks

  10. #10
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    They don't have the same sheet name, the transform is based upon the same sheet name Sheet1.

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

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