Forum Discussion

jmaikido's avatar
jmaikido
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago
Solved

importing word wrap text

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

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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