May 31 2019
- last edited on
Jul 12 2019
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:
May 31 2019 09:54 AM
@thirdredwall , that could be done with Power Query. Please check the sample attached.
Jul 29 2021 05:40 AM
Not sure I understood the question. If check step by step queries in attached to previous post file, which of them are unclear?
Jul 29 2021 06:10 AM
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.
Jul 29 2021 03:30 PM
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