Forum Discussion

Richard_Halsall's avatar
Richard_Halsall
Helper IV
3 years ago
Solved

Pivot Unpivot and Fill Down with unique identifier

Hi, i am in need of help using pivot and unpivot to create a data set.

 

I have got this far to date

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZJNC4MwDIb/SvBcsB863K7z4mDsIruIh851U5gttJ3gv1914PwArw5K4E1KyJM3WeYRD3mU+TjyKabMibRUNTdwLo5c27J1GYIoC+bfGBkJgl1IZGUr/gL5rm9CG1DSVFa4wl+8HGUenUPEvBEtnJQUxqkAhbv9KmfUVcGKojTwVBaOl2sSb8/2I+yGpMGqmwFidGH6hDIcmfllHaxcngJgfMB40mJI9WeR9h20KFQjtLjDQ6t648Xl+Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, CompletionTime = _t, Name = _t, #"Project Number1" = _t, #"P1 Start Date Adjustments" = _t, #"P1 End Date Adjustments" = _t, #"P1 Onsite Technician Number" = _t, #"P1 Commentary for Changes" = _t, #"P2 Project Number" = _t, #"P2 Start Date Adjustments" = _t, #"P2 End Date Adjustments" = _t, #"P2 Onsite Technician Number" = _t, #"P2 Commentary for Changes" = _t, #"Project Number3" = _t, #"P3 Start Date Adjustments" = _t, #"P3 End Date Adjustments" = _t, #"P3 Onsite Technician Number" = _t, #"P3 Commentary for Changes" = _t, #"Project Number4" = _t, #"P4 Start Date Adjustments" = _t, #"P4 End Date Adjustments" = _t, #"P4 Onsite Technician Number" = _t, #"P4 Commentary for Changes" = _t, #"Project Number5" = _t, #"P5 Start Date Adjustments" = _t, #"P5 End Date Adjustments" = _t, #"P5 Onsite Technician Number" = _t, #"P5 Commentary for Changes" = _t, #"Project Number6" = _t, #"P6 Start Date Adjustments" = _t, #"P6 End Date Adjustments" = _t, #"P6 Onsite Technician Number" = _t, #"P6 Commentary for Changes" = _t, #"Project Number7" = _t, #"P7 Start Date Adjustments" = _t, #"P7 End Date Adjustments" = _t, #"P7 Onsite Technician Number" = _t, #"P7 Commentary for Changes" = _t, #"Project Number8" = _t, #"P8 Start Date Adjustments" = _t, #"P8 End Date Adjustments" = _t, #"P8 Onsite Technician Number" = _t, #"P8 Commentary for Changes" = _t, #"Project Number9" = _t, #"P9 Start Date Adjustments" = _t, #"P9 End Date Adjustments" = _t, #"P9 Onsite Technician Number" = _t, #"P9 Commentary for Changes" = _t, #"Project Number10" = _t, #"P10 Start Date Adjustments" = _t, #"P10 End Date Adjustments" = _t, #"P10 Onsite Technician Number" = _t, #"P10 Commentary for Changes" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", Int64.Type}, {"CompletionTime", type date}, {"Name", type text}, {"Project Number1", Int64.Type}, {"P1 Start Date Adjustments", type date}, {"P1 End Date Adjustments", type date}, {"P1 Onsite Technician Number", Int64.Type}, {"P1 Commentary for Changes", type text}, {"P2 Project Number", Int64.Type}, {"P2 Start Date Adjustments", type datetime}, {"P2 End Date Adjustments", type datetime}, {"P2 Onsite Technician Number", Int64.Type}, {"P2 Commentary for Changes", type text}, {"Project Number3", type text}, {"P3 Start Date Adjustments", type text}, {"P3 End Date Adjustments", type text}, {"P3 Onsite Technician Number", type text}, {"P3 Commentary for Changes", type text}, {"Project Number4", type text}, {"P4 Start Date Adjustments", type text}, {"P4 End Date Adjustments", type text}, {"P4 Onsite Technician Number", type text}, {"P4 Commentary for Changes", type text}, {"Project Number5", type text}, {"P5 Start Date Adjustments", type text}, {"P5 End Date Adjustments", type text}, {"P5 Onsite Technician Number", type text}, {"P5 Commentary for Changes", type text}, {"Project Number6", type text}, {"P6 Start Date Adjustments", type text}, {"P6 End Date Adjustments", type text}, {"P6 Onsite Technician Number", type text}, {"P6 Commentary for Changes", type text}, {"Project Number7", type text}, {"P7 Start Date Adjustments", type text}, {"P7 End Date Adjustments", type text}, {"P7 Onsite Technician Number", type text}, {"P7 Commentary for Changes", type text}, {"Project Number8", type text}, {"P8 Start Date Adjustments", type text}, {"P8 End Date Adjustments", type text}, {"P8 Onsite Technician Number", type text}, {"P8 Commentary for Changes", type text}, {"Project Number9", type text}, {"P9 Start Date Adjustments", type text}, {"P9 End Date Adjustments", type text}, {"P9 Onsite Technician Number", type text}, {"P9 Commentary for Changes", type text}, {"Project Number10", type text}, {"P10 Start Date Adjustments", type text}, {"P10 End Date Adjustments", type text}, {"P10 Onsite Technician Number", type text}, {"P10 Commentary for Changes", type text}}),
#"Extracted Date" = Table.TransformColumns(#"Changed Type",{{"CompletionTime", DateTime.Date, type date}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Extracted Date", {"CompletionTime", "Name"}, "Attribute", "Value"),
#"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "AttributeType", each if Text.Contains([Attribute], "Project Number") then "ProjectNumber" else if Text.Contains([Attribute], "Start Date") then "StartDate" else if Text.Contains([Attribute], "End Date") then "EndDate" else if Text.Contains([Attribute], "Technician Number") then "TechnicianNumber" else if Text.Contains([Attribute], "Commentary") then "Commentary" else "ID"),
#"Pivoted Column" = Table.Pivot(#"Added Conditional Column", List.Distinct(#"Added Conditional Column"[AttributeType]), "AttributeType", "Value")
in
#"Pivoted Column"

 

I have attached a sample csv file here with the data on tab 'Daily Project Updates' and my required table output on tab 'Outcome'

 

I am having difficulty where there is more than 1 project per day i.e. 24/08 as I am unsure how to create a unique identifier to the allow a pivot

 

Any help would be appreciated. Many thanks

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi Richard_Halsall ,

     

    There wasn't an expected output tab in your example file (CSV can only have one tab), but I've taken a guess that you wanted something like this:

     

    Try this example query that gets your example data into the shape above:

     

    let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVDLCoMwEPyVJeeAMYnF9lovFkov0ot4SG1ahZpAkgr+faMt4qMIy8LMLjPM5DkKEUaUBSQOKKHMg6zSjbBwLo/CuKrzTIgp48s3Fk5ASPxKVe1q8QL1bm7SWNDK1k76w2wKnCO6FEtEKzs4aSWtRxxHu/2mX9xfwcmysvDUDo6Xa5r8c+qfKd9MxzGjqxJmbtEk3NdzjLauBgg5EDKTGKmhpmxQMLLUrTTyDg+jm1+AovgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, CompletionTime = _t, Name = _t, #"Project Number1" = _t, #"P1 Start Date Adjustments" = _t, #"P1 End Date Adjustments" = _t, #"P1 Onsite Technician Number" = _t, #"P1 Commentary for Changes" = _t, #"P2 Project Number" = _t, #"P2 Start Date Adjustments" = _t, #"P2 End Date Adjustments" = _t, #"P2 Onsite Technician Number" = _t, #"P2 Commentary for Changes" = _t]),
repBlankNull = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"P2 Project Number", "P2 Start Date Adjustments", "P2 End Date Adjustments", "P2 Onsite Technician Number", "P2 Commentary for Changes"}),
    unpivOtherCols = Table.UnpivotOtherColumns(repBlankNull, {"Key", "CompletionTime", "Name"}, "Attribute", "Value"),
addColumnNames = Table.AddColumn(unpivOtherCols, "columnNames", each if Text.StartsWith([Attribute], "Proj") then Text.Select([Attribute], List.Combine({{"a".."z"}, {"A".."Z"}})) else Text.Select(Text.Trim(Text.End([Attribute], Text.Length([Attribute]) - 3)), List.Combine({{"a".."z"}, {"A".."Z"}}))),
addP_Number = Table.AddColumn(addColumnNames, "P_Number", each Number.From(Text.Select([Attribute], {"0".."9"})), type number),
pivotAttribType = Table.Pivot(addP_Number, List.Distinct(addP_Number[columnNames]), "columnNames", "Value"),
groupRows = Table.Group(pivotAttribType, {"Key", "CompletionTime", "Name", "P_Number"}, {{"ProjectNumber", each List.Max([ProjectNumber]), type nullable number}, {"StartDate", each List.Max([StartDateAdjustments]), type any}, {"EndDate", each List.Max([EndDateAdjustments]), type any}, {"TechnicianNumber", each List.Max([OnsiteTechnicianNumber]), type nullable number}, {"Commentary", each List.Max([CommentaryforChanges]), type nullable text}})
in
    groupRows

     

     

     

    Pete

3 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi Richard_Halsall ,

     

    There wasn't an expected output tab in your example file (CSV can only have one tab), but I've taken a guess that you wanted something like this:

     

    Try this example query that gets your example data into the shape above:

     

    let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVDLCoMwEPyVJeeAMYnF9lovFkov0ot4SG1ahZpAkgr+faMt4qMIy8LMLjPM5DkKEUaUBSQOKKHMg6zSjbBwLo/CuKrzTIgp48s3Fk5ASPxKVe1q8QL1bm7SWNDK1k76w2wKnCO6FEtEKzs4aSWtRxxHu/2mX9xfwcmysvDUDo6Xa5r8c+qfKd9MxzGjqxJmbtEk3NdzjLauBgg5EDKTGKmhpmxQMLLUrTTyDg+jm1+AovgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, CompletionTime = _t, Name = _t, #"Project Number1" = _t, #"P1 Start Date Adjustments" = _t, #"P1 End Date Adjustments" = _t, #"P1 Onsite Technician Number" = _t, #"P1 Commentary for Changes" = _t, #"P2 Project Number" = _t, #"P2 Start Date Adjustments" = _t, #"P2 End Date Adjustments" = _t, #"P2 Onsite Technician Number" = _t, #"P2 Commentary for Changes" = _t]),
repBlankNull = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"P2 Project Number", "P2 Start Date Adjustments", "P2 End Date Adjustments", "P2 Onsite Technician Number", "P2 Commentary for Changes"}),
    unpivOtherCols = Table.UnpivotOtherColumns(repBlankNull, {"Key", "CompletionTime", "Name"}, "Attribute", "Value"),
addColumnNames = Table.AddColumn(unpivOtherCols, "columnNames", each if Text.StartsWith([Attribute], "Proj") then Text.Select([Attribute], List.Combine({{"a".."z"}, {"A".."Z"}})) else Text.Select(Text.Trim(Text.End([Attribute], Text.Length([Attribute]) - 3)), List.Combine({{"a".."z"}, {"A".."Z"}}))),
addP_Number = Table.AddColumn(addColumnNames, "P_Number", each Number.From(Text.Select([Attribute], {"0".."9"})), type number),
pivotAttribType = Table.Pivot(addP_Number, List.Distinct(addP_Number[columnNames]), "columnNames", "Value"),
groupRows = Table.Group(pivotAttribType, {"Key", "CompletionTime", "Name", "P_Number"}, {{"ProjectNumber", each List.Max([ProjectNumber]), type nullable number}, {"StartDate", each List.Max([StartDateAdjustments]), type any}, {"EndDate", each List.Max([EndDateAdjustments]), type any}, {"TechnicianNumber", each List.Max([OnsiteTechnicianNumber]), type nullable number}, {"Commentary", each List.Max([CommentaryforChanges]), type nullable text}})
in
    groupRows

     

     

     

    Pete

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

         

        No problem at all, happy to help.

        Don't forget to give a thumbs-up on any posts that have helped you 👍

         

        Pete