Forum Discussion

Insert_Key's avatar
Insert_Key
Frequent Visitor
2 years ago
Solved

Need help restructuring a table (Pivot / Unpivot?)

Hi everyone šŸ‘‹šŸ™‚   I was assigned a task right at the end of the day that feels so straightforward to me but I've just been chasing my tail on and it's doing my head in: I've been asked to transfor...
Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    Insert_Key my bad, I forgot that Table.FromRecords takes resulting columns from the very first record in the list. Then lets create tables from each group of data and combine them. Try this code. 

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    // function uses table column name to create a record of distinct values 
    fx = (tbl, col) => 
        [values = List.Distinct(Table.Column(tbl, col)),
        count = List.Count(values),
        names = if count = 1 then {col} else List.Transform({1..count}, (x) => col & " " & Text.From(x)),
        out = Record.FromList(values, names)][out],
    // column names - you may filter unwanted columns
    columns = List.Buffer(Table.ColumnNames(Source)),
    // control "group by" list, now it produces OUTPUT B from your sample. To get OUTPUT A use {"NAME"}
    to_rows = Table.Group(
        Source, 
        {"NAME", "ID"}, 
        {"x", (x) => Table.FromRecords(
            {Record.Combine(List.Transform(columns, (w) => fx(x, w)))}
        )}),
    to_table = Table.Combine(to_rows[x])
in
    to_table

     

AlienSx's avatar
AlienSx
Super User
2 years ago

OUTPUT B from your file. Read comments in code to get OUTPUT A or C

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    // function uses table column name to create a record of distinct values 
    fx = (tbl, col) => 
        [values = List.Distinct(Table.Column(tbl, col)),
        count = List.Count(values),
        names = if count = 1 then {col} else List.Transform({1..count}, (x) => col & " " & Text.From(x)),
        out = Record.FromList(values, names)][out],
    // column names - you may filter unwanted columns
    columns = List.Buffer(Table.ColumnNames(Source)),
    // control "group by" list, now it produces OUTPUT B from your sample. To get OUTPUT A use {"NAME"}
    to_rows = Table.Group(Source, {"NAME", "ID"}, {"x", (x) => Record.Combine(List.Transform(columns, (w) => fx(x, w)))}),
    to_table = Table.FromRecords(to_rows[x], null, MissingField.UseNull)
in
    to_table
Insert_Key's avatar
Insert_Key
Frequent Visitor
2 years ago

Thank you so much! That works amazingly - I've applied it to my own sample data and got it working without issue. Before I did that, I inserted your code into my working file/real data and it does everything apart from fully expand the List into columns šŸ˜ I don't understand it. Works as per the sample file up to and including "to_rows" but the "to_table" step results in only the inital five columns presenting.

 

I can click to expand the list at the "to_rows" step which adds "= Table.ExpandRecordColumn(to_rows, "x", {"OWNER", "GROUP", "ID", "NAME", "LASTJOB", "GROUP 1", "GROUP 2", "GROUP 3", "GROUP 4", "GROUP 5"}, {"OWNER", "GROUP", "ID", "NAME.1", "LASTJOB", "GROUP 1", "GROUP 2", "GROUP 3", "GROUP 4", "GROUP 5"})" as a step and works fine, but I am curious to understand your code and resolve the issue as it is much more elegant. Have you got any idea why it isn't executing properly? There's roughly 3,000 rows in my source table, if that's relevant.

 

I can't post the results as my file contains sensitive information, but the issue is that the list does not expand to new columns, meaning the table has only the original "NAME" and "ID" columns. Thanks again!