Forum Discussion
Converting single rows into multiple duplicate rows w/multiple columns
thirdredwall , that could be done with Power Query. Please check the sample attached.
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.
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
- next, stay on any cell within and query the table with Data -> From Sheet (or From Table/Range, depends on Excel version)
- Power Query editor will be opened. Select ID column, change type on Text and Unpivot other columns
- add two Index columns, one starting from 0, another from 1
- merge query wit itself on these columns
- expand only Value from resulting column
- filter Attribute column as
- remove indexes, rename columns and load result to Excel sheet