Forum Discussion

SteveMForm's avatar
SteveMForm
Helper III
2 years ago
Solved

Unpivoting table Power Query and DAX code

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

 

 

 

 

 

 

 

  • Hi lbendlin Greg_Deckler Anonymous 

    sorry for delay- Ive been off work ill for 2 weeks.

    Can anyone pick up on the 2 issues I encountered on the Power Query, listed in message 27?

    ie

    1. The mystery 'of' and corresponding null value 
    2. Removing rows (and on an ongoing basis) where there are not resources for a given work item

     

    Many thanks

29 Replies