Need help restructuring a table (Pivot / Unpivot?)

[Insert_Key]

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.

[dufoq3]

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
    CombinedFn

[Insert_Key]

Thank 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.

[dufoq3]

You're welcome 🙂

[AlienSx]

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]

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!

[AlienSx · Accepted solution]

@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

[Insert_Key]

Amazing - it works flawlessly. Thank you so much! 🏆

[Insert_Key]

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} else

from 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! 😊

[AlienSx]

@Insert_Key 

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"}))