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]

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.

[SteveMForm]

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

[SteveMForm]

Hi @lbendlin @Greg_Deckler  @ Anyone

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

Many thanks

[SteveMForm]

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.

[lbendlin]

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.

[SteveMForm]

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 🙂

[SteveMForm]

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

[Greg_Deckler]

@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 Powe...

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"