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 l...
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

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