Forum Discussion

efilipe's avatar
efilipe
Helper IV
7 years ago

New table from cell with multiple values

Hi guys,

 

I have a table like (example)

 

IDVideo title 2Filmed atType of film
1HelloCanada, BrazilSurf, Bodyboard
2Hello allFrance, Portugal, JapanSkate, Surf
3This is a videoCanada, JapanBike, Surf
4An example of titleJapan, ChileSurf, Bodyboard
5The titleArgentina, SpainSkate, Mountains, Bodyboard
6Title 4Argentina, Mexico, ChinaBodyboard, Mountains
7Yes titleChina, ArgentinaSurf, Bike, Photo
8Maybe titleFrance, Germany, Brazil, ChinaPhoto, 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:

 

IDCountry
1Canada
1Brazil
2France
2Portugal
2Japan
3Canada
3Japan
4Japan
4Chile
5Argentina
5Spain
6Argentina
6Mexico
6China
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!

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

1 Reply

  • stretcharm's avatar
    stretcharm
    Memorable Member
    7 years ago

    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"