Forum Discussion
Transforming data from two columns into sample grouped columns
Hello to all,
I am a new adventurer into data analysis, and want to learn how to use Excel more efficiently.
What I want to do is shown below - to change my existing data from 2 columns that list the name of the sample and its measurements, to a table where each sample is its own column with its measurements listed below it in rows. It's imperative that the measurements stay in their order.
I have tried to use Powerquery, but most I can do is Group the samples by their name - but this only gives me a link to table that shows the measurements for that sample.
All help is appreciated!
Best regards,
PhD Student
Hi PhDWoes,
You’re describing a classic Power Query reshape: take a 2-column list like [Sample, Measurement] and “pivot” it so each Sample becomes a separate column, with the measurements stacked in order down the rows. The key to preserving order is to create a per-Sample index first, then pivot on Sample using that index as the row key.
See this link for guidance:
Pivot columns (Power Query) - Microsoft SupportIf you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
~Taylor Amy
5 Replies
Alternatively, you could group and collect columns and names, like so
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc4pEoAwEEXBu4yOYQfJfM0JUhGISBAs96cqNk9gW3WMtlqwLe/3e+Ujn09jKVTYEnaEPeFAOBJOhHNBp6fT0+np9HR6Oj2dnv7juRQU5UV5UV6UF+VFeVFelFeVTx8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sample = _t, Measurement = _t]), GroupRows = Table.Group(Source, {"Sample"}, {{"Measurement", each [Measurement]}}), ToTable = Table.FromColumns(GroupRows[Measurement], GroupRows[Sample]) in ToTableNewStep=let a=Table.Group(YourTable,"Sample",{"n",each [Measurement]}) in Table.FormColumns(a[n],a[Sample])
Hi PhDWoes,
You’re describing a classic Power Query reshape: take a 2-column list like [Sample, Measurement] and “pivot” it so each Sample becomes a separate column, with the measurements stacked in order down the rows. The key to preserving order is to create a per-Sample index first, then pivot on Sample using that index as the row key.
See this link for guidance:
Pivot columns (Power Query) - Microsoft SupportIf you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
~Taylor Amy
Thank you so much for the help! Adding the per-sample index fixed my issues, and saved me days of work! 🙂
Hi PhDWoes , Thank you for reaching out to the Microsoft Community Forum.
tayloramy is correct, creating a per-sample index to preserve the order of measurements and then performing a pivot on the Sample column using that index as the row key is the best approach. This ensures each sample becomes its own column with measurements aligned correctly. To complete the solution, it’s worth noting that in Power Query you can add the per-sample index by grouping the data first, adding an index column within each group and then expanding and pivoting. This extra step guarantees that even if the source data changes or new measurements are added, the order is maintained accurately across all samples.
Table.Pivot - PowerQuery M | Microsoft Learn
Pivot columns (Power Query) - Microsoft Support
Thank you tayloramy for your valuable response.
