Pivot Unpivot and Fill Down with unique identifier

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