+ Reply to Thread
Results 1 to 11 of 11

Pivot repeating columns names in PQ

  1. #1
    Registered User
    Join Date
    10-14-2020
    Location
    Shanghai
    MS-Off Ver
    O365
    Posts
    55

    Pivot repeating columns names in PQ

    Hi

    i do have a log file from one equipment. Short description. Many stations, each station has a number, Temperatur, Time,..
    The log file is creating one row for the whole line.
    At the beginning of the row is see the time go in and the time go out of the line, the flightbar number (=batch) and then each station used with the parameter (temperature, time,..) for each station.

    The problem now is I want to analyse the data and get the data in a better way to do so. This means I need a table with the basic data for each Flightbar (time in, out, number, good and bad,,,) and then only one column for each of the stations (Stn;Set time;Time;Time down;Time up;Temp;Amp H with the corresponding values.
    The columns in the new format should be:
    Date;Date in;Date out;Flightbar number;Process number;Order number1;Part number1;Total area4;Following alarm exist:;Stn;Set time;Time;Time down;Time up;Temp;Amp H;

    For each flight number i will have x rows according to the number of x stations used.

    I thought I know PQ a bit but I am confused how to so this problem. I attached the example CSV file for a better understanding.

    Any thoughts from here?

    thanks a lot
    Attached Files Attached Files
    Last edited by hansolu; 04-20-2021 at 09:50 PM. Reason: solved

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,079

    Re: Pivot repeating columns names in PQ

    Please provide the workbook containing your attempt at pivoting the data using PQ so that we can troubleshoot.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    10-14-2020
    Location
    Shanghai
    MS-Off Ver
    O365
    Posts
    55

    Re: Pivot repeating columns names in PQ

    Hi,
    I don't even know how to start this task, thats my issue, sorry

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,079

    Re: Pivot repeating columns names in PQ

    OK, well, sorry, I'm not going to do this for you. The data is a complete mess and it would take hours for me to first work out what's what and then work out how to process it. Don't have the time for that - sorry.

  5. #5
    Registered User
    Join Date
    10-14-2020
    Location
    Shanghai
    MS-Off Ver
    O365
    Posts
    55

    Re: Pivot repeating columns names in PQ

    Sorry, missunderstandig. I dont want to be lazy and let work done by others. I just don't know how to start

    I played a bit and created a template which is more easy to understand.
    Basically the first columns are the basic data (column 1-30), column 31 (Stn) to column 37 (Amp H) are the values which are repeating many times for each row.
    I transformed the first samples manually into one sheet (Final Output Wish) to show it more clear.

    I don't know how to split the columns from 31 to the end to the get desired output like shown.

    I would appreciate some ideas how I should start.

    thanks a lot
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,079

    Re: Pivot repeating columns names in PQ

    Thank you for this - the fact that you have made an effort makes a big difference. I'll have another look, but I suspect it will be one of the forum members who is better at PQ than I am that will solve this.

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,612

    Re: Pivot repeating columns names in PQ

    Please try

    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-14-2020
    Location
    Shanghai
    MS-Off Ver
    O365
    Posts
    55

    Re: Pivot repeating columns names in PQ

    Hello,

    thanks a lot, nearly perfect. just the timing is mixed up, i will try to explain, just need some time to figure out how to describe
    Last edited by hansolu; 04-15-2021 at 11:05 PM.

  9. #9
    Registered User
    Join Date
    10-14-2020
    Location
    Shanghai
    MS-Off Ver
    O365
    Posts
    55

    Re: Pivot repeating columns names in PQ

    Hi,
    i found the issue.

    when doing the unpivot step, PQ skip the fields with a NULL inside, this will screw up the order at the Integer-Divided step.
    So I inserted a REPLACE null with "" to ensure every column is considered in unpivot. Then the order is fine and everything is working properly

    One thing i need to look into for myself is the Step Integer-Divided Column and the Pivot afterwards. I do not really understand this yet, but time will tell hopefully.

    Thanks a lot for your help.
    Attached Files Attached Files

  10. #10
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,612

    Re: Pivot repeating columns names in PQ

    Integer-Divided is the number to keep the same number to stay in the same row after Pivot column.

    There are neater ways to do this using Table.Group


    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    10-14-2020
    Location
    Shanghai
    MS-Off Ver
    O365
    Posts
    55

    Re: Pivot repeating columns names in PQ

    Hi

    thanks for the second option, i will try to understand. I guess the grouping function is better fit for my purpose,

    Thanks for the help, really appreciate.

+ 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. [SOLVED] Move Repeating and Non-Repeating Names into separated columns
    By aaaaa34 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-23-2019, 03:02 PM
  2. Replies: 7
    Last Post: 12-05-2016, 10:23 AM
  3. Replies: 5
    Last Post: 07-14-2016, 10:06 AM
  4. pick a name out of list of 20 names without repeating names picked
    By gammoneer2b in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-06-2014, 10:14 AM
  5. Replies: 3
    Last Post: 07-28-2014, 03:48 AM
  6. Replies: 1
    Last Post: 06-20-2012, 08:14 AM
  7. [SOLVED] repeating names in row fields of a pivot table
    By Jack N in forum Excel General
    Replies: 1
    Last Post: 08-18-2006, 11:40 AM

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