How to perform a ‘split and merge’ on columns to change (a) into (b)?
Use this power Query Code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVUISszLVorViVZySi0uUTDUMdIxAXP9MpNTFUx0THXMwFz3/PwUoCxQLhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name Rank", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Name Rank", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Name Rank.1", "Name Rank.2"}),
#"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"Name Rank.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Name Rank.2"),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Name Rank.1", type text}, {"Name Rank.2", Int64.Type}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"Name Rank.2", type text}}, "en-US"),{"Name Rank.1", "Name Rank.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
#"Merged Columns"
