Forum Discussion

chokedoke's avatar
chokedoke
Regular Visitor
8 years ago

How to report on column with multiple values split by a comma

Hi All,

 

I currently have a table with multiple columns. Each row has a unique index number, and there is one column that can have a varying amount of entries seperated by a comma. The other problem is that i dont know how many items will be in the row some only have 1 or 2 others can have as many as 10 some of which i dont care about

 

An example would be

 

ID, Title, Tags

1, Test Item, Overhead

2, Green Tree, Overhead, Housekeeping

3, Blue Tree, Housekeeping, Troubleshooting, Overhead

4, Yellow Tree, Housekeeping, Troubleshooting, Change

 

What i need to do is count up all the rows that have Overhead or Change etc.

 

I can manage this by the use of wildcards when all i want is numbers, but when i try and display this on a report i will get something like the below for Overheads

 

1 for Overheads

1 for Overheads, Housekeeping

1 for Housekeeping, Troubleshooting, Overhead

 

When what i would actually want to see is

 

3 for Overheads

3 for Housekeeping

2 for Troubleshooting

1 for Change

 

Any help on this would be appreciated!

Thanks

Need Help

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    I would think that you would need to split your column out and unpivot the columns to rows.

    • MarcelBeug's avatar
      MarcelBeug
      Icon for Community Champion rankCommunity Champion
      8 years ago

      Anonymous Lydia, it is not a good idea to split a column into new columns, if you don't now how many new columns you will get. Your solution has the "rat trap" as nicely illiustrated by Excel On Fire in this video (to which I added a comment for improvement).

       

      Otherwise my suggestion, in this case, would be to use the advanced option to split the column to rows. Your amended code could look like:

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpJLS5R8CxJzQWy/ctSizJSE1OUYnWilYyAAu5Fqal5CiFAEklWR8Ejv7Q4NTs1tSAzLx2s1hgo7ZRTmgpTiqxAByiYX5qUk1qckZ9fAhZAsccEqD4yNScnv5xI3c4ZiXnpqUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Title = _t, Tags = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Title", type text}, {"Tags", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Tags", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Tags"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Tags", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Tags", Text.Trim, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{"Tags", "Value"}})
in
    #"Renamed Columns"

       

      Split column to rows

      • chokedoke's avatar
        chokedoke
        Regular Visitor
        8 years ago

        Thanks, and that would work however i have a slight extra layer of complexity in that the table that needs the split has a one to many relationship with another table

         

        The other table contains item history and each change to a record is kept in its own row. I am currently linking by a field called WorkItemSK which in the table we are looking at here has a single record per WorkItemSK that links to the history table that has many rows with WorkItemSK

         

        So if i try to split each record into multiple rows it break this relationship.

         

        Any ideas or have i just got myself into a bad situation and i need to explore something else?