Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello everyone,
I have a timesheet report that displays as follows, where each row represents an entry (each job logged). There are columns (Mon-Sun) with the entered hours, and then columns for comments for each day which represent the "type" of job code (A, W, D, I, R, F).
I am looking for a way to total the hours for each "type", relative to the row it is in. I tried making a column: "Type A = calculate(sum('Timesheet'[Mon]),'Timesheet'[Mon - Comment]="A") + calculate(sum('Timesheet'[Tue]),'Timesheet'[Tue- Comment]="A") + etc....... which accomplished what I wanted for type A. But as soon as I tried to make the next column for the next type: "Type W = calculate(sum('Timesheet'[Mon]),'Timesheet'[Mon - Comment]="W")etc....., I was given a "circular dependency" error.
Mon | Tue | Wed | Thu | Fri | Sat | Sun | Total | Mon - Comment | Tue - Comment | Wed - Comment | Thu - Comment | Fri - Comment | Sat - Comment | Sun - Comment |
8 | 8 | 8 | -- | -- | -- | -- | 24 | I | D | R | -- | -- | -- | -- |
1 | 3 | -- | -- | -- | -- | -- | 4 | A | A | -- | -- | -- | -- | -- |
-- | 3 | -- | -- | -- | -- | -- | 3 | -- | R | -- | -- | -- | -- | -- |
7 | 2 | -- | -- | -- | -- | -- | 9 | R | I | -- | -- | -- | -- | -- |
In excel, I was able to accomplish this with the following formula, but am looking for the equivalent solution in Power BI.
Thank you!
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PU9JRCilNBZLhqSkgdkYpkHQrygSSwYklILIUrCa/JDEHSAN1KOgqOOfn5qbmlUD0ovCBpqDKZ5Si8IEmo/CBdqDyS5HNj9WJVrIAisKwri4mYWQCJDyB2AWIg7AqAhljCGQa4zACTICMcYRiHIpA5oDZeA2CS2J3DNwkc5Dj8RlkCTXDE485sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t, #"(blank).11" = _t, #"(blank).12" = _t, #"(blank).13" = _t, #"(blank).14" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","--",null,Replacer.ReplaceValue,{"Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Total", "Mon - Comment", "Tue - Comment", "Wed - Comment", "Thu - Comment", "Fri - Comment", "Sat - Comment", "Sun - Comment"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Mon", Int64.Type}, {"Tue", Int64.Type}, {"Wed", Int64.Type}, {"Thu", Int64.Type}, {"Fri", Int64.Type}, {"Sat", Int64.Type}, {"Sun", Int64.Type}, {"Total", Int64.Type}, {"Mon - Comment", type text}, {"Tue - Comment", type text}, {"Wed - Comment", type text}, {"Thu - Comment", type text}, {"Fri - Comment", type text}, {"Sat - Comment", type text}, {"Sun - Comment", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "A", each (if [#"Mon - Comment"]="A" then [Mon] else 0) +
(if [#"Tue - Comment"]="A" then [Tue] else 0) +
(if [#"Wed - Comment"]="A" then [Wed] else 0) +
(if [#"Thu - Comment"]="A" then [Thu] else 0) +
(if [#"Fri - Comment"]="A" then [Fri] else 0) +
(if [#"Sat - Comment"]="A" then [Sat] else 0) +
(if [#"Sun - Comment"]="A" then [Sun] else 0))
in
#"Added Custom"
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".
Note: I only added the "A" column. You can how it is done, and can add the other ones yourself .
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PU9JRCilNBZLhqSkgdkYpkHQrygSSwYklILIUrCa/JDEHSAN1KOgqOOfn5qbmlUD0ovCBpqDKZ5Si8IEmo/CBdqDyS5HNj9WJVrIAisKwri4mYWQCJDyB2AWIg7AqAhljCGQa4zACTICMcYRiHIpA5oDZeA2CS2J3DNwkc5Dj8RlkCTXDE485sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t, #"(blank).11" = _t, #"(blank).12" = _t, #"(blank).13" = _t, #"(blank).14" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","--",null,Replacer.ReplaceValue,{"Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Total", "Mon - Comment", "Tue - Comment", "Wed - Comment", "Thu - Comment", "Fri - Comment", "Sat - Comment", "Sun - Comment"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Mon", Int64.Type}, {"Tue", Int64.Type}, {"Wed", Int64.Type}, {"Thu", Int64.Type}, {"Fri", Int64.Type}, {"Sat", Int64.Type}, {"Sun", Int64.Type}, {"Total", Int64.Type}, {"Mon - Comment", type text}, {"Tue - Comment", type text}, {"Wed - Comment", type text}, {"Thu - Comment", type text}, {"Fri - Comment", type text}, {"Sat - Comment", type text}, {"Sun - Comment", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "A", each (if [#"Mon - Comment"]="A" then [Mon] else 0) +
(if [#"Tue - Comment"]="A" then [Tue] else 0) +
(if [#"Wed - Comment"]="A" then [Wed] else 0) +
(if [#"Thu - Comment"]="A" then [Thu] else 0) +
(if [#"Fri - Comment"]="A" then [Fri] else 0) +
(if [#"Sat - Comment"]="A" then [Sat] else 0) +
(if [#"Sun - Comment"]="A" then [Sun] else 0))
in
#"Added Custom"
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".
Note: I only added the "A" column. You can how it is done, and can add the other ones yourself .
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
127 | |
31 | |
27 | |
24 | |
22 |