May 31 2019
06:09 AM
- last edited on
Jul 12 2019
11:25 AM
by
TechCommunityAP
May 31 2019
06:09 AM
- last edited on
Jul 12 2019
11:25 AM
by
TechCommunityAP
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:
Convert to:
May 31 2019 09:54 AM
@thirdredwall , that could be done with Power Query. Please check the sample attached.
Jul 29 2021 05:25 AM
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
Oct 28 2021 06:20 AM
Got it!!!! Thank you so much for the detailed instructions!! Very helpful!
Oct 28 2021 02:14 PM
@melandiemcgee , glad to help
Jan 05 2022 10:15 AM
Jan 05 2022 12:03 PM
@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.