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
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
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.
You're welcome 🙂