Unpivoting table Power Query and DAX code

Hi Anonymous lbendlin Greg_Deckler 

So, I have done the transformation after alot of head scratching (working off Gregs M code in this post was actually not complete as per the original post- never mind).

I do have a couple of issues before moving on to the visuals and DAX side of this:

I have 2 issues in the table

1. There is a mystery 'of' under the iteration through the staff numbers in the column 'Number", which also throws a 'null' in it's corresponding 'Name' column

- I assume of have created this through some mistake in the Query- Query as follows (ignore the first chunk as previous queries- take from "Unpivoted Other Columns":

let
    Source = SharePoint.Tables("https://xxxx.sharepoint.com/teams/GRP034774924", [Implementation="2.0", ViewMode="All"]),
    #"1a5b9214-7320-41c8-afcb-9660c825e049" = Source{[Id="1a5b9214-7320-41c8-afcb-9660c825e049"]}[Items],
    #"Changed Type" = Table.TransformColumnTypes(#"1a5b9214-7320-41c8-afcb-9660c825e049",{{"Request Date", type date}, {"Start Date", type date}, {"End Date", type date}}),
    #"Extracted Values" = Table.TransformColumns(#"Changed Type", {"Architectural Areas involved", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Expanded Deputy Director" = Table.ExpandListColumn(#"Extracted Values", "Deputy Director"),
    #"Expanded Resource 1 Name" = Table.ExpandListColumn(#"Expanded Deputy Director", "Resource 1 Name"),
    #"Expanded Resource 2 name" = Table.ExpandListColumn(#"Expanded Resource 1 Name", "Resource 2 name"),
    #"Expanded Resource 3 name" = Table.ExpandListColumn(#"Expanded Resource 2 name", "Resource 3 name"),
    #"Expanded Resource 4 name" = Table.ExpandListColumn(#"Expanded Resource 3 name", "Resource 4 name"),
    #"Expanded Resource 5 name" = Table.ExpandListColumn(#"Expanded Resource 4 name", "Resource 5 name"),
    #"Expanded Resource 6 name" = Table.ExpandListColumn(#"Expanded Resource 5 name", "Resource 6 name"),
    #"Expanded Resource 7 name" = Table.ExpandListColumn(#"Expanded Resource 6 name", "Resource 7 name"),
    #"Extracted Values1" = Table.TransformColumns(#"Expanded Resource 7 name", {"BA Deliverable", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values1",{"Attachments", "Version", "App Modified By", "Color Tag", "Compliance Asset Id", "Folder Child Count", "Content Type", "Modified", "Created", "Created By", "Modified By", "Retention label Applied", "Item Child Count", "Retention label", "App Created By", "Item is a Record", "Label setting", "Label applied by"}),
    #"Expanded Requestor" = Table.ExpandListColumn(#"Removed Columns", "Requestor"),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Requestor",null,"empty",Replacer.ReplaceValue,{"Description of Work"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","","not entered",Replacer.ReplaceValue,{"Directorate or Team"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","","not entered",Replacer.ReplaceValue,{"Business Sponsor"}),
    #"Expanded External Form Requested Service" = Table.ExpandListColumn(#"Replaced Value2", "External Form Requested Service"),
    #"Replaced Value3" = Table.ReplaceValue(#"Expanded External Form Requested Service",null,"none",Replacer.ReplaceValue,{"External Form Requested Service"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3",null,false,Replacer.ReplaceValue,{"Platform Ownership"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","","tbc",Replacer.ReplaceValue,{"BA Deliverable"}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Replaced Value5", {{"BA Deliverable", "tbc"}}),
    #"Replaced Value6" = Table.ReplaceValue(#"Replaced Errors",null,0,Replacer.ReplaceValue,{"Total Consultant FTE"}),
    #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6",null,0,Replacer.ReplaceValue,{"Resource 1 Capacity"}),
    #"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7",null,0,Replacer.ReplaceValue,{"Resource 2 Capacity"}),
    #"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8",null,0,Replacer.ReplaceValue,{"Resource 3 Capacity"}),
    #"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9",null,0,Replacer.ReplaceValue,{"Resource 4 Capacity"}),
    #"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10",null,0,Replacer.ReplaceValue,{"Resource 5 Capacity"}),
    #"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11",null,0,Replacer.ReplaceValue,{"Resource 6 Capacity"}),
    #"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12",null,0,Replacer.ReplaceValue,{"Resource 7 Capacity"}),
    #"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13",null,0,Replacer.ReplaceValue,{"Capacity Requested"}),
    #"Replaced Value15" = Table.ReplaceValue(#"Replaced Value14",null,0,Replacer.ReplaceValue,{"Number of Staff Requested"}),
    #"Replaced Value16" = Table.ReplaceValue(#"Replaced Value15",null,"none",Replacer.ReplaceValue,{"Decision Rationale"}),
    #"Replaced Value17" = Table.ReplaceValue(#"Replaced Value16",null,"none",Replacer.ReplaceValue,{"Audit Change Info"}),
    #"Replaced Value18" = Table.ReplaceValue(#"Replaced Value17",null,0,Replacer.ReplaceValue,{"Cost Centre"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Replaced Value18",{"Edit", "Type"}),
    #"Replaced Value19" = Table.ReplaceValue(#"Removed Columns1",null,false,Replacer.ReplaceValue,{"Consultant Resourcing?"}),
    #"Expanded Resource 1 Name1" = Table.ExpandRecordColumn(#"Replaced Value19", "Resource 1 Name", {"title", "picture"}, {"Resource 1 Name.title", "Resource 1 Name.picture"}),
    #"Expanded Resource 2 name1" = Table.ExpandRecordColumn(#"Expanded Resource 1 Name1", "Resource 2 name", {"title", "picture"}, {"Resource 2 name.title", "Resource 2 name.picture"}),
    #"Expanded Resource 3 name1" = Table.ExpandRecordColumn(#"Expanded Resource 2 name1", "Resource 3 name", {"title", "picture"}, {"Resource 3 name.title", "Resource 3 name.picture"}),
    #"Expanded Resource 4 name1" = Table.ExpandRecordColumn(#"Expanded Resource 3 name1", "Resource 4 name", {"title"}, {"Resource 4 name.title"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Resource 4 name1",{{"Resource 5 name", type text}}),
    #"Replaced Value20" = Table.ReplaceValue(#"Changed Type1","","not specified",Replacer.ReplaceValue,{"Resource 5 name"}),
    #"Replaced Value21" = Table.ReplaceValue(#"Replaced Value20",null,"not specified",Replacer.ReplaceValue,{"Resource 5 name"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Replaced Value21",{"Resource 1 Name.picture"}),
    #"Replaced Value22" = Table.ReplaceValue(#"Removed Columns2",null,"not specified",Replacer.ReplaceValue,{"Resource 1 Name.title"}),
    #"Replaced Value23" = Table.ReplaceValue(#"Replaced Value22",null,"not specified",Replacer.ReplaceValue,{"Resource 2 name.title"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Replaced Value23",{"Resource 2 name.picture"}),
    #"Replaced Value24" = Table.ReplaceValue(#"Removed Columns3",null,"not specified",Replacer.ReplaceValue,{"Resource 3 name.title"}),
    #"Removed Columns4" = Table.RemoveColumns(#"Replaced Value24",{"Resource 3 name.picture"}),
    #"Replaced Value25" = Table.ReplaceValue(#"Removed Columns4",null,"not specified",Replacer.ReplaceValue,{"Resource 4 name.title"}),
    #"Replaced Value26" = Table.ReplaceValue(#"Replaced Value25",null,"not specified",Replacer.ReplaceValue,{"Resource 6 name"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value26",{{"Resource 6 name", type text}, {"Resource 4 name.title", type text}, {"Resource 3 name.title", type text}, {"Resource 2 name.title", type text}, {"Resource 7 name", type text}}),
    #"Replaced Value27" = Table.ReplaceValue(#"Changed Type2",null,"not specified",Replacer.ReplaceValue,{"Resource 7 name"}),
    #"Replaced Value28" = Table.ReplaceValue(#"Replaced Value27","","not specified",Replacer.ReplaceValue,{"Reason for Request"}),
    #"Replaced Value29" = Table.ReplaceValue(#"Replaced Value28","","not specified",Replacer.ReplaceValue,{"Work Item Documents"}),
    #"Replaced Value30" = Table.ReplaceValue(#"Replaced Value29","","not specified",Replacer.ReplaceValue,{"Milestones"}),
    #"Replaced Value31" = Table.ReplaceValue(#"Replaced Value30","","not specified",Replacer.ReplaceValue,{"Work Info"}),
    #"Replaced Value32" = Table.ReplaceValue(#"Replaced Value31","","not specified",Replacer.ReplaceValue,{"Work ID"}),
    #"Expanded Requestor1" = Table.ExpandRecordColumn(#"Replaced Value32", "Requestor", {"title"}, {"Requestor.title"}),
    #"Replaced Value33" = Table.ReplaceValue(#"Expanded Requestor1","","not specified",Replacer.ReplaceValue,{"Project Programme Name"}),
    #"Replaced Value34" = Table.ReplaceValue(#"Replaced Value33",null,false,Replacer.ReplaceValue,{"Service Ownership"}),
    #"Replaced Value35" = Table.ReplaceValue(#"Replaced Value34","","not specified",Replacer.ReplaceValue,{"RAG"}),
    #"Replaced Value36" = Table.ReplaceValue(#"Replaced Value35","","Not aligned to priority",Replacer.ReplaceValue,{"CS&TD Priority Type"}),
    #"Replaced Value37" = Table.ReplaceValue(#"Replaced Value36","","not specified",Replacer.ReplaceValue,{"Recommendation"}),
    #"Expanded Deputy Director1" = Table.ExpandRecordColumn(#"Replaced Value37", "Deputy Director", {"title"}, {"Deputy Director.title"}),
    #"Replaced Value38" = Table.ReplaceValue(#"Expanded Deputy Director1","","not specified",Replacer.ReplaceValue,{"Deputy Director.title"}),
    #"Replaced Value39" = Table.ReplaceValue(#"Replaced Value38","","not specified",Replacer.ReplaceValue,{"Deputy Director.title"}),
    #"Replaced Value40" = Table.ReplaceValue(#"Replaced Value39",null,"not specified",Replacer.ReplaceValue,{"Deputy Director.title"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value40",{{"Overall Score", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Requestor.title", "Requestor"}, {"Deputy Director.title", "Deputy Director"}, {"Resource 1 Name.title", "Resource 1 Name"}, {"Resource 2 name.title", "Resource 2 name"}, {"Resource 3 name.title", "Resource 3 name"}, {"Resource 4 name.title", "Resource 4 name"}}),
    #"Removed Columns5" = Table.RemoveColumns(#"Renamed Columns",{"ID", "Work ID"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns5", {"Work Ref", "Title of Work", "Description of Work", "Project Programme Name", "Request Date", "Requestor", "Group", "Directorate or Team", "Business Sponsor", "Start Date", "End Date", "Request Type", "Work Type", "External Form Requested Service", "Vision Development", "Blueprinting", "Options Development", "Scoping & Shaping", "Architectural & Design Assurance", "BA Capability (Internal)", "Service Ownership", "Platform Ownership", "Status", "RAG", "Aligned to Strategy?", "Clear Deliverable?", "Clear Architectural Value?", "Priority?", "Impact?", "Risk?", "Opportunity", "Beneficiary?", "Size Score", "Complexity Score", "Architectural Areas involved", "Priority Type", "BA Deliverable", "Key Context", "Recommendation", "Deputy Director", "Consultant Resourcing?", "Total Consultant FTE", "Capacity Requested", "Reason for Request", "Decision Rationale", "Work Item Documents", "Audit Change Info", "Cost Centre", "Re-charge?", "Overall Score", "Work Info", "Milestones"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Number", "Property"}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Split Column by Delimiter",{{"Property", Text.Proper, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Capitalized Each Word", List.Distinct(#"Capitalized Each Word"[Property]), "Property", "Value"),
    #"Removed Columns6" = Table.RemoveColumns(#"Pivoted Column",{"Attribute.1"})
in
    #"Removed Columns6"

Secondly, I have a good number of rows that are repeats of the Work Item to handle the different staff on the work item, but are redundant because there are no names against the work item.

For example, under the current query applied, all work items are split out by 7 to handle the 7 (potential) different staff allocated to that work.

In practice, we prob have a good number of work items that dont have the full 7 staff allocated. How do I remove rows where there are no staff allocated against?  I assume it is some type of Remove Column where a condition is met?

Thanks