topic Re: Unpivoting table Power Query and DAX code in DAX Commands and Tips

Unpivoting table Power Query and DAX code

SteveMForm — Sat, 16 Mar 2024 20:38:43 GMT

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

Re: Unpivoting table Power Query and DAX code

SteveMForm — Thu, 14 Mar 2024 15:42:03 GMT

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

Re: Unpivoting table Power Query and DAX code

lbendlin — Thu, 14 Mar 2024 15:55:08 GMT

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-Forum/ba-p/963216

Re: Unpivoting table Power Query and DAX code

SteveMForm — Thu, 14 Mar 2024 18:02:06 GMT

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

Re: Unpivoting table Power Query and DAX code

lbendlin — Sat, 16 Mar 2024 12:58:54 GMT

The link says I need to ask for access.

Re: Unpivoting table Power Query and DAX code

SteveMForm — Sat, 16 Mar 2024 20:35:09 GMT

should be sorted now

Re: Unpivoting table Power Query and DAX code

lbendlin — Sat, 16 Mar 2024 20:47:46 GMT

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.

Re: Unpivoting table Power Query and DAX code

SteveMForm — Sat, 16 Mar 2024 21:55:07 GMT

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

Re: Unpivoting table Power Query and DAX code

lbendlin — Sat, 16 Mar 2024 23:21:42 GMT

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

Re: Unpivoting table Power Query and DAX code

SteveMForm — Sun, 17 Mar 2024 09:08:50 GMT

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?

Re: Unpivoting table Power Query and DAX code

lbendlin — Sun, 17 Mar 2024 21:42:10 GMT

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.

Re: Unpivoting table Power Query and DAX code

SteveMForm — Mon, 18 Mar 2024 09:46:12 GMT

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

Re: Unpivoting table Power Query and DAX code

SteveMForm — Tue, 19 Mar 2024 19:02:56 GMT

Hi lbendlin - any ideas on this yet?

Thanks

Re: Unpivoting table Power Query and DAX code

SteveMForm — Wed, 20 Mar 2024 13:46:30 GMT

Hi lbendlin /Folks

Is anyone able to help further with this one?

Re: Unpivoting table Power Query and DAX code

Greg_Deckler — Thu, 21 Mar 2024 12:42:09 GMT

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.

Re: Unpivoting table Power Query and DAX code

SteveMForm — Thu, 21 Mar 2024 18:09:05 GMT

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

Re: Unpivoting table Power Query and DAX code

SteveMForm — Fri, 22 Mar 2024 08:19:14 GMT

Hi lbendlin Greg_Deckler @ Anyone

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

Many thanks

Re: Unpivoting table Power Query and DAX code

SteveMForm — Sat, 23 Mar 2024 11:36:39 GMT

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.

Re: Unpivoting table Power Query and DAX code

lbendlin — Sat, 23 Mar 2024 14:55:46 GMT

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.

Re: Unpivoting table Power Query and DAX code

SteveMForm — Sat, 23 Mar 2024 17:16:21 GMT

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 🙂