Forum Discussion
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?
ie1. The mystery 'of' and corresponding null value
2. Removing rows (and on an ongoing basis) where there are not resources for a given work itemMany thanks
29 Replies
- SteveMFormHelper III
can't work out how to attach a .pbix- anyone help?
- lbendlinSuper User
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
- SteveMFormHelper III
thanks lbendlin - link as follows, hope it works: https://drive.google.com/file/d/1dojJ7pQ56akvamBumsHtMFlBjuUCKbv_
- SteveMFormHelper III
- SteveMFormHelper III
Greg_Deckler lbendlin apologies- been on leave- will get stuck into this now
- AnonymousNot applicable
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
- SteveMFormHelper III
Hi Anonymous - 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