Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Everyone,
I have a timesheet table and I would like to slice it to show time spent on tasks for selected people/projects.
I also would like to show the total time for a project for everyone or selected people.
The issue is task and project id repeated on the table and context for the tasks come from previous rows where the person and the project is specified.
My thought was If I could split the table whenever I found the row containing the person information that would eliminate repeated values and I would have unique tables.
I am still really new to power bi and creating queries so I might be missing something obvious.
Thanks for reading, hope the question is clear.
Here is my dataset:
EVERYONE | ProjectID | TaskID | Type | Time |
ALL PROJECTS | total | 9 | ||
Project1 | 2015301 | project | 5 | |
Task1 | 2015301 | 74709878 | task | 5 |
Project2 | 2348062 | project | 4 | |
Task2 | 2348062 | 88239468 | task | 4 |
Person1 | ProjectID | TaskID | Type | Time |
ALL PROJECTS | total | 5 | ||
Project1 | 2015301 | project | 3 | |
Task1 | 2015301 | 74709878 | task | 3 |
Project2 | 2348062 | project | 2 | |
Task2 | 2348062 | 88239468 | task | 2 |
Person2 | ProjectID | TaskID | Type | Time |
ALL PROJECTS | total | 4 | ||
Project1 | 2015301 | project | 2 | |
Task1 | 2015301 | 74709878 | task | 2 |
Project2 | 2348062 | project | 2 | |
Task2 | 2348062 | 88239468 | task | 2 |
Solved! Go to Solution.
Hi @Anonymous
You may connect to csv file by improt mode. You can transform your data model in Power Query Editor.
My table:
My advanced editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tY+xCoMwFEV/RTI7JC9R41hahxZBad3EQUqG2lZFpeDfNzEipiBksBDI493DTU6eo+gjujGpBXJR2jWVuA/nk5yzsn/qYWxVlj3eAhVujg5x7KTX5BIds5sjA32GZihf8g4nZi4icgGYeBQTTbV6Lydv4tQjJhSwAIc84KpShgs5N4KCKePYh99GtjSaEOdAQ+avGjWZiq5varKPtret7Ri/pNbedNvbrARrcViJwz7izFYcrMXhL+LFFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"EveryOne", type text}, {"ProjectID", type text}, {"TaskID", type text}, {"Type", type text}, {"Time", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type1",5),
#"Added Conditional Column" = Table.AddColumn(#"Removed Top Rows", "Person", each if Text.Contains([EveryOne], "Person") then [EveryOne] else null),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "P/T", each if Text.Contains([EveryOne], "Project") then [EveryOne] else if Text.Contains([EveryOne], "Task") then [EveryOne] else null),
#"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column1",{"Person", "P/T", "EveryOne", "ProjectID", "TaskID", "Type", "Time"}),
#"Filled Down" = Table.FillDown(#"Reordered Columns",{"Person"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"EveryOne"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"P/T", Order.Ascending}}),
#"Removed Top Rows1" = Table.Skip(#"Sorted Rows",4),
#"Sorted Rows1" = Table.Sort(#"Removed Top Rows1",{{"Person", Order.Ascending}, {"P/T", Order.Ascending}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Sorted Rows1",{{"Time", Int64.Type}})
in
#"Changed Type2"
Result:
You can download the pbix file from this link: Splitting Table Whenever a Column Value is Encountered
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
You can show the sum of time by slicer.
I update your data model as below.
Build a card visual by Time column and build some slicers. As below, it shows sum of time for person1 and Type = task.
If this reply still couldn't help you solve your problem, please show me a screenshot of the result you want and it may make it easier for me to understand your requirement.
You can download the pbix file from this link: Splitting Table Whenever a Column Value is Encountered
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Rico,
Thanks for your response.
Yes, updated data model would be easy to work with however, the way csv file is created is that way and I don't have control over the exports.
How would I do that update on the power bi system rather than doing it manually or with a command line script?
Thank you,
Tuna Bicim
Hi @Anonymous
You may connect to csv file by improt mode. You can transform your data model in Power Query Editor.
My table:
My advanced editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tY+xCoMwFEV/RTI7JC9R41hahxZBad3EQUqG2lZFpeDfNzEipiBksBDI493DTU6eo+gjujGpBXJR2jWVuA/nk5yzsn/qYWxVlj3eAhVujg5x7KTX5BIds5sjA32GZihf8g4nZi4icgGYeBQTTbV6Lydv4tQjJhSwAIc84KpShgs5N4KCKePYh99GtjSaEOdAQ+avGjWZiq5varKPtret7Ri/pNbedNvbrARrcViJwz7izFYcrMXhL+LFFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"EveryOne", type text}, {"ProjectID", type text}, {"TaskID", type text}, {"Type", type text}, {"Time", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type1",5),
#"Added Conditional Column" = Table.AddColumn(#"Removed Top Rows", "Person", each if Text.Contains([EveryOne], "Person") then [EveryOne] else null),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "P/T", each if Text.Contains([EveryOne], "Project") then [EveryOne] else if Text.Contains([EveryOne], "Task") then [EveryOne] else null),
#"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column1",{"Person", "P/T", "EveryOne", "ProjectID", "TaskID", "Type", "Time"}),
#"Filled Down" = Table.FillDown(#"Reordered Columns",{"Person"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"EveryOne"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"P/T", Order.Ascending}}),
#"Removed Top Rows1" = Table.Skip(#"Sorted Rows",4),
#"Sorted Rows1" = Table.Sort(#"Removed Top Rows1",{{"Person", Order.Ascending}, {"P/T", Order.Ascending}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Sorted Rows1",{{"Time", Int64.Type}})
in
#"Changed Type2"
Result:
You can download the pbix file from this link: Splitting Table Whenever a Column Value is Encountered
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |