Replacing multiple values in a cell

samwong,

You can do this by edit query, and the final power query looke like:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/PS0ktUgjPz03MU9JRMrLQMTTSMTTRsTC3UIrVgcv7gmWBUsZmOkA1xqZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Title = _t, Genre_ids = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Genre_ids", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Genre_ids.1", "Genre_ids.2", "Genre_ids.3", "Genre_ids.4"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Title"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Columns",{"Value"},Lookup,{"Genre_ids"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Genre.name"}, {"NewColumn.Genre.name"}),
    #"Grouped Rows" = Table.Group(#"Expanded NewColumn", {"Title"}, {{"Genre_ids", each Text.Combine([Value],","), type text}, {"Genre_fullname", each Text.Combine([NewColumn.Genre.name],","), type text}})
in
    #"Grouped Rows"

Regards,

Charlie Liao

A better alternative is to split the cells to nested lists and expand these.

When splitting into columns, you get code with hard coded column names which will not be adjusted if you have more than 4 gneres (in this case).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/PS0ktUgjPz03MU9JRMrLQMTTSMTTRsTC3UIrVgcv7gmWBUsZmOkA1xqZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Title = _t, Genre_ids = _t]),
    Splitted = Table.TransformColumns(Source,{{"Genre_ids", each Text.Split(_,",")}}),
    Expanded1 = Table.ExpandListColumn(Splitted, "Genre_ids"),
    Merged = Table.NestedJoin(Expanded1,{"Genre_ids"},Lookup,{"Genre_ids"},"NewColumn",JoinKind.LeftOuter),
    Expanded2 = Table.ExpandTableColumn(Merged, "NewColumn", {"Genre.name"}, {"Genre.name"})
in
    Expanded2

I also left out the last "group by" step as I understand this is not desired.