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 UserPlease 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.
Hi lbendlin
With your code it works, however if i use my source, it doesn't work. Having this code:
let Source = Excel.Workbook(File.Contents("myPath"), null, true), 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"However I get this Error:
Expression.Error: The column 'House number' of the table wasn't found.
Details: House number
I also did it without House number and only Name and Country, then it said column "Country" not found.
Do you have any recommendations?Thank you very much in advance!