Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Splitting Table Whenever a Column Value is Encountered

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: 

EVERYONEProjectIDTaskIDTypeTime
ALL PROJECTS  total9
Project12015301 project5
Task1201530174709878task5
Project22348062 project4
Task2234806288239468task4
Person1ProjectIDTaskIDTypeTime
ALL PROJECTS  total5
Project12015301 project3
Task1201530174709878task3
Project22348062 project2
Task2234806288239468task2
Person2ProjectIDTaskIDTypeTime
ALL PROJECTS  total4
Project12015301 project2
Task1201530174709878task2
Project22348062 project2
Task2234806288239468task2

 

 

1 ACCEPTED SOLUTION

Hi @Anonymous 

You may connect to csv file by improt mode. You can transform your data model in Power Query Editor.

My table:

1.png

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:

2.png

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. 

View solution in original post

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

Hi @Anonymous 

You can show the sum of time by slicer.

I update your data model as below.

1.png

Build a card visual by Time column and build some slicers. As below, it shows sum of time for person1 and Type = task.

2.png

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. 

Anonymous
Not applicable

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:

1.png

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:

2.png

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.