New table from cell with multiple values

[efilipe]

Hi guys,

I have a table like (example)

ID	Video title 2	Filmed at	Type of film
1	Hello	Canada, Brazil	Surf, Bodyboard
2	Hello all	France, Portugal, Japan	Skate, Surf
3	This is a video	Canada, Japan	Bike, Surf
4	An example of title	Japan, Chile	Surf, Bodyboard
5	The title	Argentina, Spain	Skate, Mountains, Bodyboard
6	Title 4	Argentina, Mexico, China	Bodyboard, Mountains
7	Yes title	China, Argentina	Surf, Bike, Photo
8	Maybe title	France, Germany, Brazil, China	Photo, Bike

The problem is that I want to use "Filmed at" and "Type of film" as filters.

I'm thinking of creating a table called "Filmed at" like:

ID	Country
1	Canada
1	Brazil
2	France
2	Portugal
2	Japan
3	Canada
3	Japan
4	Japan
4	Chile
5	Argentina
5	Spain
6	Argentina
6	Mexico
6	China
7	…
8	…

Same for "Type of film".

Is there an opinion on this? And this is a solution, can you give me tips how to implement it.

I dont want to use the duplicate lines using split, because it's a very big table with a huge amount of coutries, types, and etc. It would be like 15 lines for each.

Thanks!

[stretcharm]

I would duplicate/reference the Films then use Split Columns By Delimiter to Rows. You can then join to the Films.

It is possible to do splitting in Dax see this Post http://sqljason.com/2013/06/split-delimited-row-into-multiple-rows.html

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZFBa8MwDIX/ivDZl25dt2tb2MYgUOguI+SgNmpj6trBcUazXz9Zw0k2BgajJ39Pz3ZZqoXS6pWs9bxv0WGNGjYBv4xlYd+HE5e+Hg4eQ60qXaq7DADadOY5oDuShp0PsT+j1fCGLbpEXzByI5kIec/ae2M64IXwaWqaD83Uxlzm0JKltQO64bW1BP4E0URLrAqgYdsYKf/L+iATaUTW4UwuGsfj9i2aWcjC9y6y0v21WCWLhMPyt0FBN3P0Mt9hyp2xmZk4PHLzg7oxhAAaRqspu9x81/joBXziToHDYcqf3/qFwhXdkH9qCiHwj5Oqqm8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Video title 2" = _t, #"Filmed at" = _t, #"Type of film" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Video title 2", type text}, {"Filmed at", type text}, {"Type of film", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Video title 2", "Filmed at"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns", {{"Type of film", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Type of film"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Type of film", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Type of film", Text.Trim, type text}})
in
    #"Trimmed Text"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZFBa8MwDIX/ivDZl25dt2tb2MYgUOguI+SgNmpj6trBcUazXz9Zw0k2BgajJ39Pz3ZZqoXS6pWs9bxv0WGNGjYBv4xlYd+HE5e+Hg4eQ60qXaq7DADadOY5oDuShp0PsT+j1fCGLbpEXzByI5kIec/ae2M64IXwaWqaD83Uxlzm0JKltQO64bW1BP4E0URLrAqgYdsYKf/L+iATaUTW4UwuGsfj9i2aWcjC9y6y0v21WCWLhMPyt0FBN3P0Mt9hyp2xmZk4PHLzg7oxhAAaRqspu9x81/joBXziToHDYcqf3/qFwhXdkH9qCiHwj5Oqqm8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Video title 2" = _t, #"Filmed at" = _t, #"Type of film" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Video title 2", type text}, {"Filmed at", type text}, {"Type of film", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Video title 2", "Type of film"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns", {{"Filmed at", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Filmed at"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Filmed at", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Filmed at", Text.Trim, type text}})
in
    #"Trimmed Text"