Forum Discussion
Splitting Table Whenever a Column Value is Encountered
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.