Forum Discussion
Solved
Replacing multiple values in a cell
Trying to parse a JSON object where one of the values is a list of multiple integers. In a separate table (the lookup table), I have a list of integers which map to a text string. For example (thi...
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 Expanded2I also left out the last "group by" step as I understand this is not desired.
Thank you to v-caliao-msft and MarcelBeug for your help! I combined both of your work into what I needed.
For anyone interested, here is code that creates two tables, a main (data) table and a lookup table that demonstrates what I originally set out to accomplish.
Main Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsssKi5RKMkoSk1V0lEy1FEw0lEwVorViVZKLUvNAwoB+SY6CmY6ChZg0fyUFIg6Yx0FU6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Group = _t, Numbers = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Group", type text}, {"Numbers", type text}}),
#"Split on commas" = Table.TransformColumns(Source, {{"Numbers", each Text.Split(_, ", ")}}),
#"Expand Split" = Table.ExpandListColumn(#"Split on commas", "Numbers"),
#"Lookup Values" = Table.NestedJoin(#"Expand Split", {"Numbers"}, #"Lookup Table", {"numeral"}, "number as word", JoinKind.LeftOuter),
#"Expand Lookup" = Table.ExpandTableColumn(#"Lookup Values", "number as word", {"word"}, {"number as word"}),
#"Combine Rows" = Table.Group(#"Expand Lookup", {"Group"}, { {"Numbers", each Text.Combine([Numbers], ", "), type text}, {"numbers as word", each Text.Combine([number as word], ", "), type text} })
in
#"Combine Rows"Lookup Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc09DsAgCIDRuzA71P73LMYRK4sm1toev4DdvhcIOAcWDOSE4I2Dkbs+WXuSjgX7ZGaFfBfFIqDWJyvjold7k8aGSbWzkM5YVQcr0f/IDnJd9vwH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [numeral = _t, word = _t])
in
Source