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 probl...
Need Help
MarcelBeug's avatar
MarcelBeug
Community 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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      chokedoke,

      You can re-create relationship . If you have duplicated values in relationship field in both tables, create another bridge table containing unique values in relationship field, then create relationship among the three tables.

      Regards,
      Lydia

      • chokedoke's avatar
        chokedoke
        Regular Visitor
        8 years ago

        I have heard these bridge tables mentioned before but im not really familiar with what they are or how they would be set up

         

        Could you please point me in the direction of a good example?

         

        Thanks