Converting single rows into multiple duplicate rows w/multiple columns

Copper Contributor

Need to convert data for a single person on one row with multiple columns into multiple rows for the same person plus each column of related data. Transpose does not work. Power Query might do the trick, but this is the first time using that tool.

 

Any help is much appreciated!

 

Here is the sample data to start with:

Existing table of dataExisting table of dataConvert to:

Final ResultFinal Result

9 Replies

@thirdredwall , that could be done with Power Query. Please check the sample attached.

So, how exactly did you accomplish this in Power Query? 

@melandiemcgee 

Not sure I understood the question. If check step by step queries in attached to previous post file, which of them are unclear?

Sorry. I don't have a background in Power Query. But I need to accomplish what you've done here. I see the query steps listed in the attachment but I don't know where to start to replicate what you've done. I guess I am looking for a step by step on the process--the details of what you did for each query step.

 

I have the data in an excel file and I know how to launch the power query editor. From there, I don't really know what to do. What I am looking to do is duplicate id's and create multiple rows for each test score (see attachment). This might be the wrong forum for that kind of assistance. If so, my apologies. But, any help you can provide would be appreciated.

@Sergei Baklan 

@melandiemcgee 

To work with Power Query you need to invest time to learn at least some basic things. That's not like with formulas where you may take the sample, change cell references and receive result. 

With your sample.

Power Query works with tables or named ranges. First is better, convert your data to structured table. Stay on any cell within, Ctrl+T and confirm

image.png

- next, stay on any cell within and query the table with Data -> From Sheet (or From Table/Range, depends on Excel version)

image.png

- Power Query editor will be opened. Select ID column, change type on Text and Unpivot other columns

image.png

- add two Index columns, one starting from 0, another from 1

- merge query wit itself on these columns

image.png

- expand only Value from resulting column

image.png

- filter Attribute column as

image.png

- remove indexes, rename columns and load result to Excel sheet

image.png

@Sergei Baklan 

Got it!!!! Thank you so much for the detailed instructions!! Very helpful!

This is exactly what I needed. Followed the step-by-step instructions and got just what I needed. Thanks!

@mgradie , glad to help

Just in case, if use bit of coding that could be done one more way

let
    Source   = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IDtoText = Table.TransformColumnTypes(
        Source,{{"SPRIDEN_ID", type text}}),
    Unpivot  = Table.UnpivotOtherColumns(
        IDtoText,
        {"SPRIDEN_ID"}, "Attribute", "Value"),
    // table withe Acts only
    Act     = Table.AlternateRows(Unpivot,1,1,1),
    // table with Score only
    Score   = Table.AlternateRows(Unpivot,0,1,1),
    // Combine two above
    Combine = Table.FromColumns(
        {Act[SPRIDEN_ID], Act[Attribute], Act[Value], Score[Value]},
        {"SPRIDEN_ID", "Test Desc", "Test Act", "Score" } )
in
    Combine

I believe other variants exist.