Forum Discussion
Solved
Pivot issue
Hello - this seems simple but I can't work it out. I have a table like this: SITE CODE Site A aa Site A aa Site A bb Site A cc Site A cc Site B aa Site B bb Si...
Hi naninamu, I'm not sure who is LDufour, but you wanted to reply me probably 😉
updated code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs4sSVVwVNJRSkxUitXBw09KQuUnJ+PmO6Hpd0LTTwwf2TxnNHli+KmpqPy0NATfBcgvL0flI9vnguZ+bHyQ/lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SITE = _t, CODE = _t]), Transformed = [ a = Table.Group(Source, {"SITE"}, {{"T", each {_{0}[SITE]} & [CODE] }}, GroupKind.Local)[T], b = let max = List.Max(List.Transform(a, List.Count)) in Table.FromRows(List.Transform(a, each _ & List.Repeat({null}, max - List.Count(_)))), c = Table.ColumnNames(b), d = Table.RenameColumns(b, List.Zip({ c, {"SITE"} & List.Transform(List.Skip(List.Positions(c)), each "CODE " & Text.From(_)) })) ][d] in Transformed
Hi - it ran but came back with errors. The Site column had errors in every row, and it only made 2 columns even though most Sites had more than 2 codes. Sorry I'm not that great at understanding the code - does it handle the fact that each Site has a different number of Codes? ie some Sites might have 1 code, others could have 50 for instance. Thanks for your help!!
Hi naninamu, I'm not sure who is LDufour, but you wanted to reply me probably 😉
updated code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs4sSVVwVNJRSkxUitXBw09KQuUnJ+PmO6Hpd0LTTwwf2TxnNHli+KmpqPy0NATfBcgvL0flI9vnguZ+bHyQ/lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SITE = _t, CODE = _t]),
Transformed = [ a = Table.Group(Source, {"SITE"}, {{"T", each {_{0}[SITE]} & [CODE] }}, GroupKind.Local)[T],
b = let max = List.Max(List.Transform(a, List.Count)) in Table.FromRows(List.Transform(a, each _ & List.Repeat({null}, max - List.Count(_)))),
c = Table.ColumnNames(b),
d = Table.RenameColumns(b, List.Zip({ c, {"SITE"} & List.Transform(List.Skip(List.Positions(c)), each "CODE " & Text.From(_)) }))
][d]
in
Transformed