Unpivoting table Power Query and DAX code

You can keep your SharePoint list  (and I would recommend you connect to it directly)  but in Power Query I would split it such that the Work Tracker table only has the work item columns (remove all the resource columns)  and the newly created Resources table only has the Work item ID column and the resource columns.  You can the unpivot that Resources table to make it usable.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pVbLboMwEPwXn1NkG/zgW6ocUBOlqYIa5XFIvr7BhrVxScATCSEsMeP17Ox6Pz+ZLD84LwVbMV7Yx7vZbB/v+We9GrDSYc3jvbue87CVWvR/itJuR0k78kJQ5LzgHell3y4gMtD2FkLVFLSPLf5exKC4Q1XRUYMEyxh8muWcQNNgiRxblRCqglCQm5RGUJpDKAGhIOU1pLyGlNeQ8hpTHipaDRWtrhGUgbxhIG8YyBsG8oaBvGEgbxjIGwbyhoG8YSBvWN+CRXe8TXPPBMsAzu7ftnwHXAXwcXvJHBGseufQ/raHZhNrCAtEbdkwWuTrld72Gdg6DGNeLB9D2xy+FlNItnw8SLHlG6FXFG7+vlCfqKE+UUN9ovZJDZMuLzqtemfFQ+hrIsE5Gzsrlsx/n6/32wIiQYDcuhI85Cq7NgT3RekGyd8TS8fx7vtn37Y3Eqk/T7+4XJvThf48fu8PtGib0y7aJySKFwtSHYCWGIdhuWLJ6O0Cn+NBurwQgs467C5SrUaKPMmAI6w6QkUKDIQ65ghnexGZI8p2vkNl35AOVZPDXmk9iZUcxypLmlCdquciT2WQ15ZEpvruCPsuPH8tBAf5Xmxn/p/GlgHbW8eypOPEHSTU3L+6opobp0impBnRiewp0m3p+07nw+F6G9l6NlOBKUwGA1MZC6IiA7jFk6a6Xv8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Work Tracker_Work Ref" = _t, #"Work Tracker_Resource 1 Capacity" = _t, #"Work Tracker_Resource 1 Name" = _t, #"Work Tracker_Resource 2 Capacity" = _t, #"Work Tracker_Resource 2 name" = _t, #"Work Tracker_Resource 3 Capacity" = _t, #"Work Tracker_Resource 3 name" = _t, #"Work Tracker_Resource 4 Capacity" = _t, #"Work Tracker_Resource 4 name" = _t, #"Work Tracker_Resource 5 Capacity" = _t, #"Work Tracker_Resource 5 name" = _t, #"Work Tracker_Resource 6 Capacity" = _t, #"Work Tracker_Resource 6 name" = _t, #"Work Tracker_Resource 7 Capacity" = _t, #"Work Tracker_Resource 7 name" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Work Tracker_Work Ref"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Work Tracker_Resource ","",Replacer.ReplaceText,{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Work Tracker_Work Ref", type text}, {"Attribute.1", Int64.Type}, {"Attribute.2", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"Attribute.2", Text.Proper, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Capitalized Each Word", List.Distinct(#"Capitalized Each Word"[Attribute.2]), "Attribute.2", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Capacity", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Capacity] <> null)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Attribute.1", "Resource"}})
in
    #"Renamed Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.