Forum Discussion
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 transform a table so that a system user's details can be viewed on a single row instead of spanning multiple rows. I felt that the answer would lie with Pivot and/or Unpivot, but ran out of patience (and skill!) before I was unable to create the desired results... Pivoting/Unpivoting data confuses me every single time! I've added a small set of dummy data and the result that I am chasing below.
Sample:
| OWNER | GROUP | ID | NAME | LOG |
| HRTEAM | CREATE | HR_123 | JOHN SMITH | 1/06/2024 |
| HRTEAM | EDIT | HR_123 | JOHN SMITH | 1/06/2024 |
| HRTEAM | DELETE | HR_123 | JOHN SMITH | 1/06/2024 |
| HRTEAM | IMPORT | HR_123 | JOHN SMITH | 1/06/2024 |
| HRTEAM | EXPORT | HR_123 | JOHN SMITH | 1/06/2024 |
| HRTEAM | LEGAL | HR_XYZ | JOHN SMITH | 1/06/2024 |
| HRTEAM | DIRECTOR | HR_XYZ | JOHN SMITH | 1/06/2024 |
Result:
| OWNER | ID 1 | ID 2 | NAME | LOG | GROUP 1 | GROUP 2 | GROUP 3 | GROUP 4 | GROUP 5 | GROUP 6 | GROUP 7 |
| HRTEAM | HR_123 | HR_XYZ | JOHN SMITH | 1/06/2024 | CREATE | EDIT | DELETE | IMPORT | EXPORT | LEGAL | DIRECTOR |
My mind went to Pivot / Unpivot but if anyone has a better or preferred solution, please pitch in - I'm not constrained to using Pivot / Unpivot at all. Thanks in advance for your help! 😊
EDIT: Here is a link to a file containing the dummy data; hopefully you can still download and edit even though the document is set to read-only on OneDrive. Please let me know if there are any issues.
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
9 Replies
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_tableThank 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_table
Hi Insert_Key, different approach:
Result
If you want to group by different columns, you have to edit GroupedRows step, but keep in mind that you have to change both lists (they should be the same):
Whole code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ggKcXX0VdJRcg5ydQxxBTI8guINjYyBDC9/Dz+FYF/PEA8gx1DfwEzfyMDIRClWB0mXq4tnCKl6XFx9XEm3ydM3wD+IZLtcI8jR5ePq7ugD0RQRGUWstzyDXJ1D/IOI0RcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OWNER = _t, GROUP = _t, ID = _t, NAME = _t, LOG = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"LOG", type date}}), GroupedRows = Table.Group(ChangedType, {"OWNER", "NAME", "LOG"}, {{"All", each _, type table}, {"fn", each [ tbl = Table.FirstN(Table.SelectColumns(_, {"OWNER", "NAME", "LOG"}), 1), idList = List.Buffer(List.Distinct([ID])), groupList = List.Buffer(List.Distinct([GROUP])), idAcc = List.Accumulate({0..List.Count(idList)-1}, tbl, (s,c)=> Table.AddColumn(s, "ID " & Text.From(c+1), (x)=> idList{c}, type text)), groupAcc = List.Accumulate({0..List.Count(groupList)-1}, idAcc, (s,c)=> Table.AddColumn(s, "GROUP " & Text.From(c+1), (x)=> groupList{c}, type text)) ][groupAcc], type table}}), CombinedFn = Table.Combine(GroupedRows[fn]) in CombinedFnThank you very much for your solution, dufoq3, I really appreciate it 🤗 I'm far closer to being able to understand the code in the first reply though, so I will adopt that method in the hope that I can learn its structure and remember it for the next time that I need to undertake a similar task. It's always interesting to see different approaches; thanks again.
You're welcome 🙂
