Forum Discussion
Need help restructuring a table (Pivot / Unpivot?)
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
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
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!
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_tableAmazing - it works flawlessly. Thank you so much! 🏆
Sorry to be a PITA, AlienSx, but I'm asking for little more help if possible, please.
When I got to the office this morning and had a look at the output using my real data, I realised that where a user had only a single Group associated with their Name or Name/ID (depending on which Option was used) the Group would be listed under a column named "Group" where if a User had multiple Groups, they would start populating across from "Group 1". Ideally they'd all follow the same structure, either starting at "Group" or "Group 1" and then a sequence "Group 2", "Group 3" and so on. I couldn't figure out how to adjust the code to start the sequence at from "2" rather than "1" so I did something inelegant and removedif count = 1 then {col} elsefrom your fx step, and each column title now starts with a "1" appended - whether there are multiples or not. Like I said, inelegant... but it's definitely workable.
What I failed to understand yesterday is that the raw data that will be exported and presented to me on a regular basis has fairly meaningless headers/column titles that need modifying. Some contain carriage returns/line break. I have spent quite a bit of time Googling and trying to get my head around the various possible functions that could be used but haven't been able to change the headers without corrupting the output in one way or another. Please can give me some guidance on how to approach this either integrating into your solution or either an extra step(s) above below it? I've updated the sample file to contain a new table ("NEW_SOURCE") with some ugly headers for replacing with those in the other tables and my initial post.
I really appreciate your help! 😊