Results 1 to 1 of 1

Thread: Help to Transpose of sorts in PowerQuery

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Junior Member
    Join Date
    May 2022
    Posts
    1
    Rep Power
    0

    Help to Transpose of sorts in PowerQuery

    Hi,


    I have a table which shows annual leave for employees. each line is for an event, so there are 3 core columns for each line event, start date, end date, duration and employeeID. The rest of the columns are other information that is required but those 4 columns listed are the unique events that would be used I think to separate a line item. what I would like to do within PowerQuery is get that split out so there is a line entry for each day booked.

    In my live file my data is a PowerQuery table connection and nota table in the workbook like in this file. Obviously I cannot send the file with the external links because it would not load for you. I have only added the tables in this sample file to allow me to turn it into a PowerQuery connection as you can see. It is that table connection that I require to “transposed" in some way by those key events but directly in PowerQuery.

    For example. if an annual leave or absence event was for 3 days, 01/05/2022-03/05/2022, in my current table it shows that in 1 line with the duration column reading 3. Plus all the other info such as names etc.
    what I would like is for there to be 3 lines showing this event. Line 1 would read 01/05/2022, duration 1, plus all the other info such as names etc. Line 2 would read 02/05/2022, duration 1, plus all the other info such as names etc. Line 3 would read 03/05/2022, duration 1, plus all the other info such as names etc.

    I do not want the output table in a workbook tab, just as a Table connection in PowerQuery.

    I hope that makes sense, it felt hard to explain. I have attach the new data file sample to support,

    Many thanks in advance.
    Attached Files Attached Files

Similar Threads

  1. more about PowerQuery aka Get&Transform (links)
    By sandy666 in forum ETL PQ Tips and Tricks
    Replies: 0
    Last Post: 06-10-2020, 07:43 PM
  2. test transpose alternative Transpose with Index
    By DocAElstein in forum Test Area
    Replies: 3
    Last Post: 08-29-2014, 08:29 AM
  3. RefersTo:=Range [transpose(..)]
    By PcMax in forum Excel Help
    Replies: 1
    Last Post: 02-06-2014, 04:41 AM
  4. Transpose A Column Of Data In To A Table
    By gunjan.nasit in forum Excel Help
    Replies: 4
    Last Post: 05-20-2013, 12:33 AM
  5. Transpose data into Rows
    By vikash200418 in forum Excel Help
    Replies: 2
    Last Post: 04-10-2012, 11:02 PM

Posting Permissions

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