Forum Discussion
Split multiple columns into one row
Ah, now it is clear. Here is one of many ways to do that
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8svMzkksjsnzyk+NyQvJz1XSUTI0iskzNInJM7JIBPLcU4tyE/MqY/LcihLzkoGKggsSM/OUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"House Number" = _t, Country = _t]), mix = List.Zip({Text.Split(Source[Name]{0},"#(lf)"),Text.Split(Source[House Number]{0},"#(lf)"),Text.Split(Source[Country]{0},"#(lf)")}), #"Converted to Table" = Table.FromList(mix, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Name", "House Number", "Country"}) in #"Split Column by Delimiter"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.
Super UserHi Anonymous - if possible can you please upload some sample data along with expected output.
- lbendlin
Please show the expected outcome based on the sample data you provided.
Hi lbendlin ,
I have. See the first screenshot. All the data is in one line / cell divided by Line breaks. But I don't want only one line with line breaks but 3 Lines.
Do you mean that, or do I get something wrong?- lbendlin
Ah, now it is clear. Here is one of many ways to do that
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8svMzkksjsnzyk+NyQvJz1XSUTI0iskzNInJM7JIBPLcU4tyE/MqY/LcihLzkoGKggsSM/OUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"House Number" = _t, Country = _t]), mix = List.Zip({Text.Split(Source[Name]{0},"#(lf)"),Text.Split(Source[House Number]{0},"#(lf)"),Text.Split(Source[Country]{0},"#(lf)")}), #"Converted to Table" = Table.FromList(mix, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Name", "House Number", "Country"}) in #"Split Column by Delimiter"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.