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
Amazing - 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 removed
if 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! 😊
regarding Group, Group 2 etc: try this:
if count = 1 then {col} else {col} & List.Transform({2..count}, (x) => col & " " & Text.From(x))regarding ugly headers: if columns always follow the same order then smth like this should work:
Table.RenameColumns(Source, List.Zip({Table.ColumnNames(Source), {"OWNER", "GROUP", "ID", "NAME", "LOG"}))