Tab delimited file imported from API imports as one cell with line feeds removed

[pmc086]

I have an API that I am connecting to which returns tab delimited text.

When I run the API query in Postman I get a response resembling the following response (note: extract - the actual response can be thousands of lines):

FR AE0570 T 2272.22 0 12714.02 7500 13784 22000 Professional development (admin)
FR AE0700 T 1243.52 1666.67 8778.48 6666.68 24684 20000 Motor Vehicle Expenses
FR AE0710 T 29890.88 39682.5 143827.49 136035 302065 377800 Other Administration Costs
FR AE0770 T 484.44 833.33 3022.3 3333.32 8203 10000 Postage
FR AE0780 T 784.79 1083.34 4414.22 4333.36 12063 13000 Bank charges
FR AE0950 T 16803.02 16803.030000000002 67212.08 67212.12000000001 209352 204357.55 Insurance

 If I do the same query in Power Query, the response is imported as text into one column on one row in a table in the following format:

FR AE0570 T 6608.51 4500 50809.05 22000 0 22000 Professional development (admin) FR AE0700 T 1212.94 2500 25662.59 30000 0 30000 Motor Vehicle Expenses FR AE0710 T 8872.409999999998 19647 397270.65 332950 0 332950 Other Administration Costs FR AE0770 T 1001.91 1300 13622.03 14000 0 14000 Postage FR AE0780 T 644.18 1750 13146.7 21000 0 21000 Bank charges FR AE0950 T 16803.030000000002 14950 187181.09000000003 179422 0 179422 Insurance

 As you can see, the line feeds appear to be removed by Power Query.

I need to split this cell into rows and columns and have trouble splitting into rows as I do not have a delimiter that I can split on.

Has anyone come across this issue before and found a solution? Maybe a different way to import my data?

I've been looking around in this and other forums and have yet to find a solution.

Thanks!

[artemus]

In the advanced editor you code would look something like

let 
   Source = Web.Contents(...),
   AsText = Binary.ToText(Source),
   AsLines = Text.Split(AsText, "#(lf)"),
   AsRows = List.Transform(AsLines, each Text.Split(_, "#(tab)"),
   AsTable = Table.FromRows(AsRows)
in
   Source

[pmc086]

Something like that.

let
Source = Web.Page(Web.Contents(...)),
Data = Source{0}[Data],
Children = Data{0}[Children],
Children1 = Children{1}[Children],
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Children1, {{"Text", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Text")
in
#"Split Column by Delimiter"

I have to drill down to body of the response and have used the tools on the ribbon to perform the split. I haven't bothered doing the tab delimiter yet as there's no point if I couldn't get the line feed delimiter to work.

[artemus]

Might be easier if you go back a step, right click the single cell, and choose "Drill Down"

This will put you in text mode instead of table mode, which should be easier to change into the table you want.

[pmc086 · Accepted solution]

Found a solution.

Instead of the source being Web.Page, used CSV.Document.

Now I just have to work out how to get the commas that form some of the text in the columns to import properly...

I'll do my own research of this though and post a new question if needed as it isn't related to the original post question.