Split multiple columns into one row

[Nks_Mr]

Hi Guys,

first off - sorry, I am quite new to PowerBI. I want to know how I can split up these entries (all in one line, divided by line breaks) intro three lines with 1, Niklas, 12, Germany / 2, Joe, 14, French / ...

I know that their is this tool "Divide Columns", however doing this for every column, I end up with 27 rows (3*3*3).

I would be so grateful for any ideas / help.

Best Regards

[rajendraongole1]

Hi @Nks_Mr - if possible can you please upload some sample data along with expected output. 

[Nks_Mr]

Hi @rajendraongole1

Hope this is sufficient.

[lbendlin]

Please show the expected outcome based on the sample data you provided.

[Nks_Mr]

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 · Accepted solution]

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.

[Nks_Mr]

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!

[lbendlin]

Power Query is case sensitive.  House Number, not House number.

[Nks_Mr]

@lbendlin yes, but then I have the same problem. This is my table.

and this the code:

let
    Source = Excel.Workbook(File.Contents("..."), 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 still get this mistake: 

Do I miss something?

[lbendlin]

Please post a sample version of your Excel file.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[Nks_Mr]

Name	House Number	Country
Niklas Joe John	12 14 28a	Germany Spain France

[lbendlin]

You missed promoting the first row as headers.

[Nks_Mr]

Hi @lbendlin 

Is Google Drive ok as well? 

https://docs.google.com/spreadsheets/d/1bB-HN2uYMncPviu1kjzZ29YyyrlPTuWl/edit?usp=drive_link&ouid=11...

[lbendlin]

In general yes, but your link says "Access denied". Please check the sharing settings.