Unpivoting table Power Query and DAX code

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?

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.

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

Hi lbendlin - any ideas on this yet?

Thanks

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.

Hi Greg_Deckler - thanks for giving some input on this

On replacing the source step, novices like me just need a little bit more direction: so if we take the query above, I need to know what actual text I need to replace- is it just between the "", do I need the Json.Document and Binary encoding stuff?

Need to also check where I am getting the source ID from is correct. I have this in the query window under 'Source':

= SharePoint.Tables("https://xxx.sharepoint.com/teams/GRP034774924", [Implementation="2.0", ViewMode="All"])

and this under 'Navigation' (which I assume is the specific list ID):

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

2. The suggestion has been to split the Work Tracker table in order to pivot both this and the new table.

Work Traker is basically: each Work Item = a SharePoint Item in the list.

A whole bunch of values are captured against each Work Item, including who is resourced on it and what capacity for each person (up to 7 resources on any Work Item).

If I split off the Resource Name and Capacity Columns from by Work Tracker table into a new table, the advice is to delete the same columns in the Work Tracker table they were split from:

I assume this does not cause any issues, and if I delete these columns from Work Tracker, whenever I need to refresh the data, it will update across the split tables correctly? I also didn't know if I needed to create any table relationships between the two split tables?

3. You'll need to see the previous post as the the measure that was suggested as solution.

It very helpfully suggested the dax to create a measure based on a dummy table and data I provided, and was provided in a .pbix file- 'worked':

Worked = 
var a = SUMMARIZECOLUMNS('work tracker'[Name],'work tracker'[Start Date],'work tracker'[End Date],'work tracker'[Capacity],'Calendar'[Date])
var b = ADDCOLUMNS(a,"sm",if([Start Date]<=[Date] && [Date]<=[End Date],[Capacity]))
return sumx(b,[sm])

Now having provided the actual table structures in the file link in this post (albeit with alot of values stripped out and names anonymised)  ( https://drive.google.com/file/d/1dojJ7pQ56akvamBumsHtMFlBjuUCKbv_ ) but also now with the splitting of tables and removing columns etc, I am suspecting the previous measure won't work and needs updating.

The goal is to show capacity used in the team at any point in time (default to show by month) where capacity used is a sum at any single point in time of the resources on 'Work Items'- happy to explain further

Hi lbendlin Greg_Deckler  @ Anyone

Would appreciate some help to get to the solution on this one.

Many thanks

Ok, so kinda disappointed in how this forum seems to run.

I've supported users in different types of technology over the years and always took a vested interest in making sure they got the support they needed. Once you start being involved, see it through type approach, be responsive.

I guess I'll need to try and finish this one myself. Thanks for the input I did get.

Fair points, but please keep in mind that this forum is where users help each other, time permitting, and to the best of their abilities. You changed the scope and complexity of your request quite a bit, to the point where it exceeded my abilities (as an example). Maybe start over with a simpler ask. 

If you need this covered professionally you can engage with one of the Microsoft partners in your area.

Hi lbendlin thanks for your response and appreciate the reflection.

I think the scope was always the same, ie the problem I was trying to solve, it just got more involved as it unpacked.

The last query I had on what bit of the power query do I need to replace/ need still seems kinda basic.

Appreciate others help time permitting, but when they don't respond but are online, I personnaly just need a - 'I don't know, it's beyond me' response. Obviously the limit of ability is a fair shout, and good to let people know.

I might well look to engage a Microsoft partner and prob can through work although it will be protracted- I cant afford this personnaly if it costs.

A simple ask now would be- what do I replace in this and the previous posts Power Query with my own source ID, and do I have the right data as my source ID (see message 15 here)

At least I can do the pivots and split the tables then 🙂

Hi lbendlin Greg_Deckler 

Just to repeat the simple request:

How (and what) do I replace the source in the query in this post with my source?

I also need to know the same for the unpivoting in the previous related post (the original subject and question in this post), although it will want to work for the full table in the .pbix file in this post in message 4.
Thanks

SteveMForm OK, the simplest thing to do is:

1.  Create a new query and connect to the source that you want.
2.  Copy the Source and Navigation steps from the query using the Advanced Editor. 
3. Open the existing query in Advanced Editor and replace the Source step
4. Adjust subsequent steps as necessary

So, let's say that you have this for your current query:

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"

and your SharePoint query looks something like this:

let
  Source = SharePoint.Tables("https://xxx.sharepoint.com/teams/GRP034774924", [Implementation="2.0", ViewMode="All"]),
  Navigation = Source{[Id="1a5b9214-7320-41c8-afcb-9660c825e049"]}[Items]
in
  Navigation

You copy the Source and Navigation lines from this query and you then open the other query. You select the Source line (all of it) in the query. You paste these two lines over top of it. You then need to adjust the name of the next step's reference to the previous step. This is all highlighted in the following text. If you need this spelled out even further, then I'm afraid I'm out, this is about as clear as I can make it.

The red gets replaced by the green. Remember to add a comma at the end of the "Navigation" row. Since the next step "#"Unpivoted Other Columns"" referenced Source and now we need to reference Navigation, then we need to change the reference (also shown red to green). 

Now, there may be other change s you need to make, I have no idea. I recommend getting yourself a good book on M that shows you how to do these things, like this one: Amazon.com: The Definitive Guide to Power Query (M): Mastering Complex Data Transformation with Power Query: 9781835089729: Gregory Deckler, Rick de Groot, Brian Julius, Melissa de Korte: Books

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]),

Source = SharePoint.Tables("https://xxx.sharepoint.com/teams/GRP034774924", [Implementation="2.0", ViewMode="All"]),
Navigation = Source{[Id="1a5b9214-7320-41c8-afcb-9660c825e049"]}[Items],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(SourceNavigation, {"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"