Can I export data from Power BI to SharePoint List?

[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

[H_Insights · Accepted solution]

I have a found workaround.

[SahilMSpower]

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

[H_Insights]

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.  )
4. Click on ellipses  and choose "Edit"  , the main development page will load.
5. Click on New and select "instant cloud flow" . The app should sign you in or connect your to the PBI service (if not already).
6.  Click on "Next Step"  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"  then type in the search box "Create Item"  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. 

    

Click Save and Apply.

That's about it. Take it for test.

H

[DennisT]

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:

[H_Insights]

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?

[takolota]

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

[DennisT]

Perfect - thx.

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

[Luckey]

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]

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!

[takolota]

@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]

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!

[acao63]

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

[Anonymous]

Hi, there is a limitation of 1001 rows when I use this power automate. 

Is there anyway we can increase the size of data which is moving to sharepoint list? 
Ideally we would want more data to be going into to sharepoint list. 

Thanks!

[avk1999]

Hi,

did you find the solution?

[KRider]

I actually did! check out my Query against a Power BI step in Automate:

So, this will produce a JSON. From there you need to nail the Schema in order for you to output to a List.

Here is the Schema I used.

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "ProductionEmployees[MTID]": {
                "type": "string"
            },
            "ProductionEmployees[Name]": {
                "type": "string"
            },
            "ProductionEmployees[Job Title]": {
                "type": "string"
            },
            "ProductionEmployees[Shift]": {
                "type": "string"
            },
            "ProductionEmployees[Line]": {
                "type": "string"
            },
            "ProductionEmployees[Labour Code]": {
                "type": "string"
            },
            "ProductionEmployees[Shop]": {
                "type": "string"
            },
            "ProductionEmployees[Cost Center]": {
                "type": "string"
            },
            "ProductionEmployees[Reports To]": {
                "type": "string"
            }
        },
        "required": [
            "ProductionEmployees[MTID]",
            "ProductionEmployees[Name]",
            "ProductionEmployees[Job Title]",
            "ProductionEmployees[Shift]",
            "ProductionEmployees[Line]",
            "ProductionEmployees[Labour Code]",
            "ProductionEmployees[Shop]",
            "ProductionEmployees[Cost Center]",
            "ProductionEmployees[Reports To]"
        ]
    }
}

 And then this was the end result:

So absolutely possible. May need to brush up on JSON and queries. Definitely worth it and it cut down my flow by over an hour!

I hope this helps.

[polymathy2017]

Huh? 

[takolota]

@KRider 

For removing double entries, if the union( ) expression method isn’t sufficient because the duplicates may not match exactly, you can use something like this to get only distinct records based on set columns

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Get-Distinct-Records/td-p/2191533

[yadukrishnankv9]

Hello @KRider

Thank you for the work around, this might solve my issue.

However, could you please explain how to write the JSON schema for a different scenario?

I am trying to copy a table from Power BI to Sharepoint List. I have copied the DAX Query from Power BI and is using the flow as shown in the attachment.

Thank you, help is deeply appreciated. 

[KRider]

Hey Krish,

you can attempt to generate from a sample or you can write based on your output.

definitely going to be a type array and then you want to break down your "items"

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {

remember to include your required at the end:

"required": [
            "ProductionEmployees[MTID]",
            "ProductionEmployees[Name]",