Can I export data from Power BI to SharePoint List?

(1) Share

Report

Posted on by H_Insights

Hello,

I have a power bi report published in my workspace. the data source is from a dataflow.

My aim is to export a specific table to a SharePoint list (copy data from the table to the list). Is it possible? Any ideas?

Thanks

Categories:

General topics

I have the same question (0)

All responses (28)

Answers (1)

Verified answer

H_Insights 79 on at

Like (2)

Report

I have a found workaround.

Was this reply helpful? Yes No
SahilMSpower 15 on at

Like (0)

Report

Hi @H_Insights ,

Did you get the solution for your requirement?
i need it too.
Please share the steps with us if you are able to export power BI table into the sharepoint list.

Thanks,
SISSPADMIN

Was this reply helpful? Yes No
H_Insights 79 on at

Like (2)

Report
Hi @SahilMSpower

Yes, it is very easy.

First make sure you have created a list in SharePoint with the columns you want to export from PBI (with the correct data type).

Open your report in Power BI desktop.
Create a new page (or in any existing one) open the visualizations pane and add "Power Automate for Power BI".
Add your columns and measures to PA visual (i.e.  )

Click on ellipses and choose "Edit"  , the main development page will load.

Click on New and select "instant cloud flow" . The app should sign you in or connect your to the PBI service (if not already).

Click on "Next Step"  to start building your flow.
Depending on your requirement you will start the development, but let assume you want to export data based on a condition to your SP list. (I would highly suggest to do this step, it's like a filter to limit the data exported).
Type "Condition" in the search box, and add that as a step. Based on your requirement you will define this filter.
Once you add a "Condition", you would notice that this step became a child of a parent step "Apply to each". that's expected.
In the "Yes" condition, click on "Add Action"  then type in the search box "Create Item"  and select it.
Again, the app should sign you in (if not already).

Select the "Site Address" and "list Name" and that should load your columns from SP. Start inserting your PBI columns into the fields.

Click Save and Apply.

That's about it. Take it for test.

H

Was this reply helpful? Yes No
DennisT 6 on at

Like (0)

Report

Hi,
really appreaciate your solution. I ran into a follow-up problem. When I choose the needed fields in “Create Item” it instantly switches to “Apply to all”. How can I set filter on Values (columns from PBI) in Power Automate?
The flow currently copies <300 line items instead of the prefiltered ~25 of my PBI table.

">10d" should be filtered:

Was this reply helpful? Yes No
H_Insights 79 on at

Like (1)

Report
The "Apply to each" is an expected/normal action and to filter your data you need to use a Condition (screen below).

I am not entirely sure what's in your ">10d" field in PBI, but I assumed it would only have a value if it's overdue >10 days? therefore the rest of the values will be blank. So basically you want to copy only rows/data that are overdue (have values).

Based on that assumption, please try the below and it should get you your ~25 records.

try the following expression in your condition (it's case sensitive and based on your column/field name '>10d').

empty(items('Apply_to_each')?['>10d'])

overall it will look like below

if my assumption is wrong, maybe you can share more context on the >10d field?

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (1)

Report

@H_Insights @DennisT @SahilMSpower

Also if you’re moving a lot of Power BI data, you may be able to insert any PBI array output into SharePoint batch update & batch create actions:
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Batch-Update-SharePoint-List-With-External-Data/td-p/1365410

Was this reply helpful? Yes No
DennisT 6 on at

Like (0)

Report

Perfect - thx.
Yes the filter contains values. Needed to adapt it a little bit but works now as expected!!!😀🙌

Was this reply helpful? Yes No
Luckey on at

Like (1)

Report

I have set up my flow and it only export 1000 records to the Sharepoint list. I believe this is due to a PowerBI visual limit. However, I wonder if anyone else is having this problem and have come up with a different way to overcome. Perhaps exporting the first thousand and then running again on the next thousand?

Was this reply helpful? Yes No
H_Insights 79 on at

Like (0)

Report

Unfortunately, the export limit is true and it's an integration issue between Power Automate and Power BI.

However, recently Microsoft introduced ExecuteQueries support in Power Automate, which you can use to overcome the export limits. The export should be in CSV.

ExecuteQueries REST API restricts query results to 100,000 rows or 1,000,000 values per query (whichever is encountered first) and the number of queries to 120 per minute.

Happy exporting!

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (0)

Report

@H_Insights

If you can export the data somewhere as a CSV, then you may be able to use this CSV parser to send the data to a list or to any other datasource.
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/td-p/1508191

Was this reply helpful? Yes No