Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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. 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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.