Split column and Combine Columns

amitchandak — Thu, 07 Apr 2022 16:14:37 GMT

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"

topic Split column and Combine Columns in Quick Measures Gallery

Split column and Combine Columns