Power Query - Transpose Multiple Rows into Single Column

%3CLINGO-SUB%20id%3D%22lingo-sub-3016719%22%20slang%3D%22en-US%22%3EPower%20Query%20-%20Transpose%20Multiple%20Rows%20into%20Single%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3016719%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20working%20on%20tracking%20application%20data%20and%20have%20a%20large%20data%20set.%20I%20have%20multiple%20rows%20for%20the%20same%20Requisition%20that%20need%20to%20be%20housed%20within%20the%20same%20row.%20See%20example%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22t0mato_0-1638250508354.png%22%20style%3D%22width%3A%20625px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F330267i7CDD01CABD6C0B6C%2Fimage-dimensions%2F625x47%3Fv%3Dv2%22%20width%3D%22625%22%20height%3D%2247%22%20role%3D%22button%22%20title%3D%22t0mato_0-1638250508354.png%22%20alt%3D%22t0mato_0-1638250508354.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20detailed%2C%20Requisition%20Number%20FULLT071366%20has%204%20rows%20of%20data%2C%20I%20would%20like%20these%20all%20listed%20for%20the%20same%20requisition%2C%20in%20a%20single%20line.%20Only%20the%20data%20regarding%20%5BStep%5D%2C%20%5B%23%20Candidates%20in%20Current%20Step%5D%20and%20%5BAverage%20Days%20in%20Current%20Step%5D%20For%20example%2C%20like%20below%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22t0mato_1-1638250820580.png%22%20style%3D%22width%3A%20489px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F330268i6970DD147CB952CF%2Fimage-dimensions%2F489x11%3Fv%3Dv2%22%20width%3D%22489%22%20height%3D%2211%22%20role%3D%22button%22%20title%3D%22t0mato_1-1638250820580.png%22%20alt%3D%22t0mato_1-1638250820580.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ENotice%20how%20each%20new%20row%20for%20the%20requisition%20is%20given%20its%20own%20column%20within%20the%20same%20row.%20How%20can%20I%20achieve%20this%20with%20Power%20Query%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20the%20data%20here%20with%20the%20first%20row%20of%20data%20being%20an%20expected%20output.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3016719%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hello!

 

I am working on tracking application data and have a large data set. I have multiple rows for the same Requisition that need to be housed within the same row. See example below:

 

t0mato_0-1638250508354.png

 

As detailed, Requisition Number FULLT071366 has 4 rows of data, I would like these all listed for the same requisition, in a single line. Only the data regarding [Step], [# Candidates in Current Step] and [Average Days in Current Step] For example, like below:

t0mato_1-1638250820580.png

Notice how each new row for the requisition is given its own column within the same row. How can I achieve this with Power Query? 

 

I've attached the data here with the first row of data being an expected output.

 

Thanks in advance!

 

 

 

 

2 Replies

@t0mato I would consider a pivot table (see attached), Then you can easily line-up similar steps as well. Obviously, the first row (that you had already extended manually is not correct now.

@Riny_van_Eekelen This is actually very useful and helpful. I think this works. I will look into this to make sure I understand how you did this.