Pivot/Unpivot to Restructure Data

[Richard_Halsall]

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

[BA_Pete · Accepted solution]

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

[Richard_Halsall]

Hi @BA_Pete , many thanks worked perfectly

[BA_Pete]

No problem. Thanks for providing the example data in such a quick/easy to use format 🙂

Pete