importing word wrap text

[jmaikido]

Hi folks I'm at a road block. Imagine I'm importing a .xlsx file that has 2 columns, Col A and Col B. Col A text is not wrapped but Col B text is and there is NO delimiter between the the words that should contain a delimeter. When the file is imported some kind of internal transformation takes place since the Query Editor displays (for example) multiple rows for the wrapped text that appear to be recognized. eg. Col B contains multiple rows  representing the seperated words but ALL within the one row.

As imported into PBI

           Col A       Col B

row1   word       word1

                          word2

                          word3

row2   word       word1

                          word2

Before import row1, Col B contained "word1word2word3"

What query editor wizardy could I possibly apply?

TIA John

[v-shex-msft · Accepted solution]

Hi @jmaikido,

I'd like to suggest you do text.split and expand function in query editor to extract the value to new row, it may fix your issue.

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtIBkQpJySkKySmpSrE6QFGIIJyfClYFUgRUAxGNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Col2]," ")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Col2"})
in
    #"Removed Columns"

Regards,

Xiaoxin Sheng