Forum Discussion

GibbE155's avatar
GibbE155
Copper Contributor
Jul 18, 2020

Reorganised a long format table to a wide data table

Hi, I'm struggling to reorganise a data table from long format to a wide format. my data is organised as follows: Value  Sample 3456434 1 56645677 1 46356 1 24556 2 235478 2...
Excel
GibbE155's avatar
GibbE155
Copper Contributor

Hi OwenPrice , 

Thanks, this appears to be the best solution. How do you return the workbook to a normal worksheet after this? The power query editor doesn't appear to allow independent sorting of columns.

Many thanks  

SergeiBaklan's avatar
SergeiBaklan
MVP
Jul 19, 2020

GibbE155 

Added sorting to my variant

let
    Source = Excel.CurrentWorkbook(){[Name="Range"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Grouped Rows" = Table.Group(#"Promoted Headers", {"Sample"},
        {{"Count", each List.Sort(_[Value ])}}),
    Custom1 = Table.FromColumns(#"Grouped Rows"[Count],
        List.Transform(#"Grouped Rows"[Sample], each "Sample " & Text.From(_)))
in
    Custom1

 

Resources