Forum Discussion
Pivot/Unpivot to Restructure Data
Hi, I really need some help with restructuring this data in power query, the image below shows what I am trying to achieve:
Group by 'Site Date', sum 'Working' & 'Onsite' values whilst listing 'Technicians' by 'TechnicianIndex' - please note the Technician Index may be more than Tech 6 on any given day
Sample raw data is here
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZCxDoMgFEV/pWE2ig+wdixov6CbcWhMk7r4/2NBGRq8vJThDYRz4MA0CdU2sm9IkhaVeL6Xz7Yu62u7+5UMU5OJG5dWzFVWsECgXZBeuJXckBGKb3BAUIdApUlYYJKwwCSpbNK1NruSNmHDIoM4wyEjVmloDMGATZgfzzxxfHh170f/VfM4n6453qanG452Kd0dtMmWd3F+/wXTNqWJo11K+1+Zvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SiteDate = _t, Technician = _t, Working = _t, Onsite = _t, TechnicianIndex = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SiteDate", type date}, {"Technician", type text}, {"Working", type number}, {"Onsite", type number}, {"TechnicianIndex", type text}})
in
#"Changed Type"
As always any help/guidance much appreciated. Thanks
Hi Richard_Halsall ,
Try the following example code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZCxDoMgFEV/pWE2ig+wdixov6CbcWhMk7r4/2NBGRq8vJThDYRz4MA0CdU2sm9IkhaVeL6Xz7Yu62u7+5UMU5OJG5dWzFVWsECgXZBeuJXckBGKb3BAUIdApUlYYJKwwCSpbNK1NruSNmHDIoM4wyEjVmloDMGATZgfzzxxfHh170f/VfM4n6453qanG452Kd0dtMmWd3F+/wXTNqWJo11K+1+Zvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SiteDate = _t, Technician = _t, Working = _t, Onsite = _t, TechnicianIndex = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"SiteDate", type date}, {"Technician", type text}, {"Working", type number}, {"Onsite", type number}, {"TechnicianIndex", type text}}), groupSiteDate = Table.Group(chgTypes, {"SiteDate"}, {{"data", each _, type table [SiteDate=nullable date, Technician=nullable text, Working=nullable number, Onsite=nullable number, TechnicianIndex=nullable text]}, {"Working", each List.Sum([Working]), type nullable number}, {"Onsite", each List.Sum([Onsite]), type nullable number}}), expandData = Table.ExpandTableColumn(groupSiteDate, "data", {"Technician", "TechnicianIndex"}, {"Technician", "TechnicianIndex"}), pivotTechIndex = Table.Pivot(expandData, List.Distinct(expandData[TechnicianIndex]), "TechnicianIndex", "Technician") in pivotTechIndex-1- Groups on date to get the day totals of [Working] and [Onsite] while retaining while table nested.
-2- Expand [Technician] and [TechIndex] back out from nested table.
-3- Pivot [TechIndex] with [Technician] as values and no aggregation.
Output:
Pete
3 Replies
Hi Richard_Halsall ,
Try the following example code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZCxDoMgFEV/pWE2ig+wdixov6CbcWhMk7r4/2NBGRq8vJThDYRz4MA0CdU2sm9IkhaVeL6Xz7Yu62u7+5UMU5OJG5dWzFVWsECgXZBeuJXckBGKb3BAUIdApUlYYJKwwCSpbNK1NruSNmHDIoM4wyEjVmloDMGATZgfzzxxfHh170f/VfM4n6453qanG452Kd0dtMmWd3F+/wXTNqWJo11K+1+Zvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SiteDate = _t, Technician = _t, Working = _t, Onsite = _t, TechnicianIndex = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"SiteDate", type date}, {"Technician", type text}, {"Working", type number}, {"Onsite", type number}, {"TechnicianIndex", type text}}), groupSiteDate = Table.Group(chgTypes, {"SiteDate"}, {{"data", each _, type table [SiteDate=nullable date, Technician=nullable text, Working=nullable number, Onsite=nullable number, TechnicianIndex=nullable text]}, {"Working", each List.Sum([Working]), type nullable number}, {"Onsite", each List.Sum([Onsite]), type nullable number}}), expandData = Table.ExpandTableColumn(groupSiteDate, "data", {"Technician", "TechnicianIndex"}, {"Technician", "TechnicianIndex"}), pivotTechIndex = Table.Pivot(expandData, List.Distinct(expandData[TechnicianIndex]), "TechnicianIndex", "Technician") in pivotTechIndex-1- Groups on date to get the day totals of [Working] and [Onsite] while retaining while table nested.
-2- Expand [Technician] and [TechIndex] back out from nested table.
-3- Pivot [TechIndex] with [Technician] as values and no aggregation.
Output:
Pete
Hi BA_Pete , many thanks worked perfectly
No problem. Thanks for providing the example data in such a quick/easy to use format 🙂
Pete
