Forum Discussion
Helper IIIFiltering duplicates when splitting row by delimiter
Hello,
I have a dataset similar to the following
| Project ID | Owner | Category | Hours |
| 1 | Adam | Apple, Orange | 5 |
| 2 | Susan | Carrot | 12 |
| 3 | John | Orange, Carrot | 3 |
| 4 | Adam | Apple, Carrot | 7 |
In order to create visuals based on the category, I have to split the data rows by delimeter, resulting in the following
| Project ID | Owner | Category | Hours |
| 1 | Adam | Apple | 5 |
1 | Adam | Orange | 5 |
| 2 | Susan | Carrot | 12 |
| 3 | John | Orange | 3 |
| 3 | John | Carrot | 3 |
| 4 | Adam | Apple | 7 |
| 4 | Adam | Carrot | 7 |
However, this makes it difficult to create a visual based on the owner data and hours.
I am looking to create a chart that shows the breakdown of hours per owner, but disregarding the duplicate values and only counting on a per project basis.
If anyone has any insight into this please let me know, thanks!
10 Replies
- Daniel29195
Community Champion
try this measure :
measure =sumx ( values ( tbl_name[owner] ) , calculate ( max(tbl_name[hours]) )
let me know if this helps .
NB :i would prefer that if you can leave your original table as it is.
and duplicate it . where the duplicated table you would expand it to multiple rows. ( and you remove the hours from it since the hours is on the owner, level ) .
so you will be having 2 tables :
table 1 :
project id , ownver , hours
tabl2 :
project id, owner , cateogry --> where category is expanded on multiple columns .
now if table 1 have uniquw ownver, then you can link it as 1 to many to the second table.
let me know if this helps .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠:
ChrisR22Daniel29195 thank you for the thoughtful response. Unfortunately I will also need to be able to visualize hours on a category level as well, is there no way to accomplish this without splitting this input multiple tables?
- Daniel29195
how the calculation should be done ?
i mean ,1 Adam Apple, Orange 5 apple and orange will take each 2.5 hours ? or5 hours each ?
Hi, ChrisR22
According to your description, I have processed the data in PowerQuery to get the data table you expect to realize, you can refer to the following method.
M code:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlHwdFHSUfIvz0stAtLOiSWp6flFlUCmR35pUbFSrE60kiGQ55iSmKsAogsKclJ1FPyLEvPSU4F8U7AKIyAruLQ4MQ9sRFFRfgmQYWgEljMGMr3yM0BSEF06CnAlxmAVJlDzEcbDFZgrxcYCAA==", 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]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project ID", Int64.Type}, {"Owner", type text}, {"Category", type text}, {"Hours", Int64.Type}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Category", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Category.1", "Category.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Category.1", type text}, {"Category.2", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Category.1"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Category.2", "Category"}}), #"Appended Query" = Table.Combine({#"Renamed Columns", #"Table (3)"}), #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Project ID", Order.Ascending}}), #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each [Category] <> null and [Category] <> "") in #"Filtered Rows"Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
- ChrisR22
Anonymous thank you for the response, I am not too familiar with M code, is there a more layman/rudimentary explanation you could provide?
Hi, ChrisR22
You can refer to the following steps to complete your needs.1. Initial table
2.Split column by delimiter3. Copy Table 1, get Table 1(2)
4.delete the Table 1's Category.1 and the Table 1 (2) of Category.2 columns , and the two tables will be the remaining Category.1 and Category.2 columns renamed Category
5. Click Table 1, use append queries/append queries as new function, will be Table 1 and Table 1 (2) merger
6. Click on the Category column of the triangle logo, pull out the drop-down box, and select the Remove Empty function to remove the blank rows
7. Click the triangle symbol in the Project ID column, Sort Ascending, and you're done.Translated with DeepL.com (free version)
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
