Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Richard_Halsall
Helper IV
Helper IV

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

Richard_Halsall_0-1738232768155.png

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

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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:

BA_Pete_0-1738234222985.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
BA_Pete
Super User
Super User

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:

BA_Pete_0-1738234222985.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi @BA_Pete , many thanks worked perfectly

 

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

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.