Transforming data from two columns into sample grouped columns

[PhDWoes]

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 

[v-hashadapu]

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.

[wdx223_Daniel]

NewStep=let a=Table.Group(YourTable,"Sample",{"n",each [Measurement]}) in Table.FormColumns(a[n],a[Sample])

[m_dekorte]

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
    ToTable

[tayloramy · Accepted solution]

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 Support

If 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

[PhDWoes]

Thank you so much for the help! Adding the per-sample index fixed my issues, and saved me days of work! 🙂