cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
H_Insights
Advocate I
Advocate I

Can I export data from Power BI to SharePoint List?

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

1 ACCEPTED SOLUTION

Accepted Solutions
H_Insights
Advocate I
Advocate I

I have a found workaround.

View solution in original post

12 REPLIES 12
H_Insights
Advocate I
Advocate I

I have a found workaround.

SahilMSpower
Frequent Visitor

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

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).

 

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

     

  4. Click on ellipses  and choose "Edit"  H_Insights_2-1647977926912.png, the main development page will load.

     

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

     

  6.  Click on "Next Step" H_Insights_4-1647978142217.png to start building your flow.
  7. 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).
  8. Type "Condition" in the search box, and add that as a step. Based on your requirement you will define this filter. 
  9. Once you add a "Condition", you would notice that this step became a child of a parent step "Apply to each". that's expected.
  10. In the "Yes" condition, click on "Add Action" H_Insights_5-1647978668860.png then type in the search box "Create Item" H_Insights_6-1647978730631.png and select it.
  11. Again, the app should sign you in (if not already).

  12.  

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

    H_Insights_8-1647978989021.png

 

Click Save and Apply.

 

That's about it. Take it for test.

 

H

 

 

 

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:

DennisT_0-1656596333808.png

 

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'])

 

H_Insights_1-1656615602632.png

 

overall it will look like below

H_Insights_0-1656615114556.png

 

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

 

@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-Extern...

Perfect - thx.

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

Luckey
Frequent Visitor

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?

H_Insights
Advocate I
Advocate I

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!

H_Insights_0-1657653560437.png

 

@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 

KRider
Resolver II
Resolver II

Hey guys, this is really amazing and super simple but I have 2 questions.

How can I put this on a schedule?

How can I check for double entries?

 

Thank you so much!

Hello @H_Insights,

 

I have come across your solution and found that helpful in my case. Could you please share the following steps for the process you shared above? Your help would be much appreciated!

 

A

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (1,811)