Unpivoting table Power Query and DAX code

[SteveMForm]

Hi Folks 

reaching back out to @lbendlin.

You sweated for me on this one a little while ago:

Solved: How to show Staff Capacity against Work by Month - Microsoft Fabric Community

When I add your query and select 'done' it comes up with the table in the 'worked.pbix' you attached to previous post.

I'm just trying to implement now and have got a bit stuck, first issue was the power query didn't work for my source:

Expression.Error: Invalid binary encoding.
Details:
    1a5b9214-7320-41c8-afcb-9660c825e049

I replaced this part of your query highlighted in red:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdNbb4IwGAbgv9JwresBEL01cUvMku1uF8aLTj5ZM6BLW5b579eC5WjExNAPYngfXsrhELBwSUhIg0XwIdU3ckMGBmlTnc8olSXYCwxTghlhoZ0pjt0Y2fFZQWqXtzzjdiFPiT2+g1HNGbPHPS9At2e3f8eFRzCPYFdEIRUMJRRTdkOyBWUG4Unf5W63lZ/1HM47otg73J8VLFPZIJomqM8PCSaJF+xUKaAhgKqTol5qNOmio9K7lpW3uFvAHy9ECUiBrnKjxx6KSdcIv4KGsfM1jAGJB7hWUqFPldY9gAtd9wTNRnGCFyV/6ypeq7xqQ3e5keXMjhgT1p7gytDCIFneE7BJB11oPGjk8RY2npBMvg/EMy7KMYTilWfsRVFcmrV98EEnDxFi4gmujtOXEtoUXKMfrsxlUoPdmtHoM/Wvnvbjo7n44z8=", BinaryEncoding.Base64),

with what I thought was the source taken from previous queries on the data:

= Source{[Id="1a5b9214-7320-41c8-afcb-9660c825e049"]}[Items]

Thats when I got the expression error.

Please can I get some help with this?

I had 2 follow up queries that I'll ask at the same time now:

1. The slimmed down table I gave in the previous post and which the 'worked.pbix' was generated from- my actual table in the report is bigger- do I need to alter the query to ensure all relevant columns are unpivotted? I have attached a skeleton PBI file which has all the columns in so you can see them- any steer on this welcome)

2. You recommended filtering the DAX outputs you had written "Next step would be to filter a or b with NETWORKDAYS". 

- for a month, or a week, I would need to divide by net working days, but if I needed to check on a single given day, this 'month' logic would not be needed, I am thinking.

- how would the DAX be extended to handle this? I have included the dimdates table in the attached pbix file as well.

Thanks - and let me know if you are on buy me a coffee or equivilant

[SteveMForm]

@Greg_Deckler @lbendlin apologies- been on leave- will get stuck into this now

[v-jianpeng-msft]

Thanks the solutions from @lbendlin and @Greg_Deckler 

Hi，@SteveMForm 

Have you tried Greg_Deckler suggestions? In the existing query, create a new blank query that will link to your SharePoint file. Then replace the source of the existing query with the M code of the query, which is aimed at the following problem:
How (and what) do I replace the source in the query in this post with my source?

Best Regards

Jianpeng Li

[SteveMForm]

Hi @v-jianpeng-msft - I'm working through it query by query- there are some possible issues but this could be with the orignial Power Query, rather than it's implementation.

One of the first things I have gone back to doing is replacing blanks and null values in the Work Tracker data table, to try and avoid having to split the table if possible, and as some of the query steps produced erros (eg Capitalising a null value as it isnt text).

I have a spearate Post here related to it: How can I Replace null values in Person-based colu... - Microsoft Fabric Community as it seems difficult to replace values in a SharePoint based Person column which I have transfored to extract records in but can't replace the null values.

WIll post tomorrow

[SteveMForm]

@v-jianpeng-msft solved the nulls and blanks- will move on tomorrow to applying the queries also as some column names have changed due to expanding the name columns as they were records so the query needs updating

[SteveMForm]

Hi @v-jianpeng-msft @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

[lbendlin]

Please note that you can do the text replace across multiple columns. No need to do it one by one.

[SteveMForm]

Hi @lbendlin /Folks

Is anyone able to help further with this one?

[SteveMForm]

can't work out how to attach a .pbix- anyone help?

[lbendlin]

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[SteveMForm]

thanks @lbendlin - link as follows, hope it works: https://drive.google.com/file/d/1dojJ7pQ56akvamBumsHtMFlBjuUCKbv_ 

[lbendlin]

The link says I need to ask for access.

[SteveMForm]

should be sorted now

[lbendlin]

You have work items without any resources.  How should these be presented?  If you want the resources to be unpivoted you have to have data in at least one of them.

[SteveMForm]

hi there @lbendlin  well- in reality - we will have some work items we log that are potential  work that will happen, and so wont hold a resource against them at that point in time, but may allocate a resource at a later date. 

How do I need this presented- well, they wont feature in a capacity view, or will have a 'nil' effect, but we would need to be able to allocate resource at a future time and the code to pick up and slot into the capacity report

[lbendlin]

I would recommend you use separate tables.  One for the projects and the other for the assigned resources.

[SteveMForm]

I was just thinking it may be a limiting factor if I understand how you are approaching unpivoting.

So, to confirm, what data are are you suggesting is in each table, and is the creation of the separate tables in Power BI, not at source?

At the moment, all items are held in one SharePoint list in the data source, and preference would be to keep it that way.

Is it possible to split the data in Power BI, creating the two tables?

[lbendlin]

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.

[SteveMForm]

Hi @lbendlin 

the code above works in so far as it creates the new table with resource and capacity by work ref as you stated- thank you.

I have some follow up queries:

1. The pbix file I provided was the table structure I have, but was a based on a copy of the SharePoint list source with had dummy names in to ensure it was anonymised data. When I run your code on my actual data, I get the names Jim, Gus etc which are not the actual names.

How do I get the code to work on my actual source data?

2. How do remove the resource name and capacity columns from the Work Tracker table. and unpivot it?

3. WIll the previous code you provided in 'worked.pbix' work given the data is now split into two tables? If not, what code do I need to do the calcualtion, inclduing removing any non-working days based on the dimDates table?

Many thanks

[SteveMForm]

Hi @lbendlin - any ideas on this yet?

Thanks

[Greg_Deckler]

@SteveMForm Responding to your private message. Coming into this super late. The names of people are actually in what looks like an enter data query. This is in the Source step. You need to replace that Source step with the actual source, like your SharePoint list. If the list has the same structure, column names, etc then everything will work just fine.

I have no idea about question 2. If you are referring to the table that you end up with, you can just remove those 2 columns. What are you unpivoting?

3. No idea, came into this late so not sure what this is referring to.