Forum Discussion

PhDWoes's avatar
PhDWoes
New Member
10 months ago
Solved

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 

Need Help
  • tayloramy's avatar
    tayloramy
    10 months ago

    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

5 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    10 months ago

    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
  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    10 months ago

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

  • tayloramy's avatar
    tayloramy
    Super User
    10 months ago

    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's avatar
      PhDWoes
      New Member
      10 months ago

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

  • v-hashadapu's avatar
    v-hashadapu
    Community Support
    10 months ago

    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.