Display CSV from API

[tvinko]

Hi all,

I'm new to PowerQuery and having troubles with displaying CSV that I get from API.  I'm using Business Central connector that returns me table with CSV text in rows.

Is there any way to extract this CSV dynamically and create table for each CSV that I get from API?

thanks

[KNP]

I'm sure there is. Can you provide a sample file, some detail about what you've tried so far and the error/issues you are experiencing?

It's a bit hard to answer with the detail you've given.

[tvinko]

Hey @KNP  , tnx for reaching out.

I'm connecting to BC web service to get the response. I got 3 columns and one of them is column with CSV content (myreport).

My Advanced Editor shows following Power Query code:

let
    Source = Dynamics365BusinessCentral.ApiContents(null, null, null),
    Sandbox = Source{[Name="SANDBOX"]}[Data],
    #"CRONUS NL" = SANDBOX{[Name="CRONUS NL"]}[Data],
    Advanced = #"CRONUS NL"{[Name="Advanced"]}[Data],
    #"Mycomp/mycomp/v1.0" = Advanced{[Name="Mycomp/mycomp/v1.0"]}[Data],
    myreports_table = #"Mycomp/mycomp/v1.0"{[Name="myreports",Signature="table"]}[Data]
in
    myreports_table

If I add following code to the auto-generated one:

...
...
...
fieldID = 0,
csv = myreports_table[myreport]{fieldID}

in
    Table.PromoteHeaders(Csv.Document(csv))

I got query, but just for first row (fieldID=0)

I would need to loop through all rows from API response and generate queries dynamically

Thanks 

[KNP]

It's a bit of a guess and if it doesn't work I might need some screen shots at your 'myreports_table' step.

But try this first...

Change your above line to...

csv = myreports_table{fieldID}[myreport]

 I think fieldID needs to come before the column name.

Maybe. 🤔

[tvinko]

This is response that I get from BC:

Of course there could be many rows with CSV content in ReportInCSV column.

I get all csv texts from main table into list, but I need to loop through list and for each csv and create new table dynamically

[KNP]

I don't feel like I have enough of a complete picture.

Are you able to post the complete M code as one block of code and a complete screen

shot when you've selected the last auto generated step?

Just blur/obfuscate any sensitive info.

I feel like this should be easy to solve, I just don't have enough info.