Extract data from API into Power BI

[LoredanaC]

Hello!

Please help me. How can I extract data from Portfolio_URL. If a add a custom column and use web.Contents formula it's bring me a new Column with Binary Result, but after that I don't know what I have to do. Please help me.

[v-tangjie-msft]

Hi @LoredanaC ,

1.Parse the Binary Data: After you've added a custom column with the formula and received binary data, you'll need to parse this binary data. You can use the function to convert the binary JSON response into a list or record. For example, add a new step to transform the column. Replace with the name of your column containing the binary  data.

Web.ContentsJson.Document= Table.TransformColumns(#"Previous Step", {{"YourColumnName", each Json.Document(_), type record}})"YourColumnName"

2.Expand the Data: If the JSON document is a record (similar to a dictionary or an object in other programming languages), you might need to expand this to get to the individual fields. Use the option in the Power Query Editor to do this. If the JSON document is a list of records, you might first need to convert it to a table using the function or the button in the Power Query Editor, and then expand the records.

Expand ColumnTable.FromListTo Table

3.Load the Data: Once you have expanded the data into a format that Power BI can work with, you can then load it into your Power BI model for further analysis and visualization.

You can refer to the similar posts that may be helpful to you: 

How to pull data from Web API into PowerBI using A... - Microsoft Fabric Community

Web.Contents with more than one URL - Microsoft Fabric Community

How to get data from REST API (JSON) in Power BI? - Microsoft Fabric Community

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[LoredanaC]

Please help. How can I extract the xlsx tables from this list of URL? I have been obtained this list of URLs. I have a column with multiple rows containing lists of URLs. Thanks!