Can I export data from Power BI to SharePoint List?

Like (1) Share

Report

Posted on 19 Mar 2022 16:17:12 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

I have the same question (0)

All responses (28)

Answers (1)

macrozier 2 on 19 Apr 2024 at 20:20:59

Like (0)

Report

Re: Can I export data from Power BI to SharePoint List?

I am very new to Flows and Power Automate. When I get to this step there are no options to select my Power BI items and I cannot figure out why. What am I doing wrong? What Condition are you entering? Why do you need that?

Was this reply helpful? Yes No
KRider 577 Super User 2024 Season 1 on 06 Feb 2024 at 20:28:39

Like (0)

Report

Re: Can I export data from Power BI to SharePoint List?

I don't think there would be a problem at all. The JSON step is just reading whatever you give it. Coming up with the Schema takes time but it isn't impossible.

Was this reply helpful? Yes No
celomf 2 on 06 Feb 2024 at 20:24:35

Like (0)

Report
Re: Can I export data from Power BI to SharePoint List?

Hi, KRider!
I want to take advantage of the development of this solution to ask what JSON editing would be like if the query in Power BI contains measures and one-to-many relationship tables. An example:

DEFINE
VAR SharepointExport =
FILTER(
DISTINCT(
SELECTCOLUMNS(
FactMeets,
"id", FactMeets[id_hash],
"source", RELATED('YLUR_metadata'[source]),
"chat date", RELATED('YLUR_metadata'[datetime_ini]),
"detection", [RiskDetection], //measure
"risk", [RiskCalc], //measure
"analyst", RELATED(DimAnalysts[Analyst])
)
),
[risk] >= 10 && [chat date] >= DATE(2024, 1, 1)
)
EVALUATE
SharepointExport

Is it possible to reference constructed table 'SharepointExport' and its columns in the JSON step?

Thank you!

Was this reply helpful? Yes No
KRider 577 Super User 2024 Season 1 on 17 Nov 2023 at 11:11:02

Like (0)

Report

Re: Can I export data from Power BI to SharePoint List?

Glad to see it Krish! Good luck with everything! That tool is very powerful.

Happy Friday!

Was this reply helpful? Yes No
yadukrishnankv9 12 on 16 Nov 2023 at 19:17:13

Like (1)

Report

Re: Can I export data from Power BI to SharePoint List?

Thank you @KRider for prompt response.

I have no clue what was wrong, it worked when I removed that specific column from the JSON script. Anyways it wasn't absolutely necessary. The rest of the floe is working fine now.

Thank you

Was this reply helpful? Yes No
KRider 577 Super User 2024 Season 1 on 15 Nov 2023 at 17:52:26

Like (1)

Report
Re: Can I export data from Power BI to SharePoint List?

Hey buddy :D,

So I think the problem is the column isn't spelled exactly like that? 'Query1[Transfer Date]' doesn't exist in your BI query. There are a few reasons it could be giving this error. Maybe it isn't in the table you are using? Or Maybe you are saving the transfer date as the Title?(The Title has to be a "required" word.

If also needs to be in your specification query table

DEFINE VAR _tminfo = CALCULATETABLE( SUMMARIZECOLUMNS('Employees'[MTID],'Employees'[Name],'Employees'[Job Title],'Employees'[Shift],'Employees'[Line],'Employees'[Labour Code],'Employees'[Shop],'Employees'[Cost Center],'Employees'[Department],'Employees'[Email],'Employees'[Reports To],'Employees'[TM Status],'Employees'[Reports To Email],'Employees'[Reports To MTID]) ) EVALUATE _tminfo

Was this reply helpful? Yes No
yadukrishnankv9 12 on 15 Nov 2023 at 16:15:23

Like (0)

Report

Re: Can I export data from Power BI to SharePoint List?

Thank you @KRider for your support!

I had the schema solved, thanks for your help. However I have 2 more questions.

1. The Schema is working well now, but there seems to be issue with Create Item; error as follows.

Also, I have Schema as below. In "required" I have only first 2 columns as other columns might have empty values.

2. The data I am copying from Power BI has Date Columns too, but I understand JSON does not have a date data type. Hence, I will be forced to convert the date in PBI to "String" then parse to JSON String type and store in Sharepoint List as a "String". This would mean that any operations that need to used Date datatype will not be possible. Do you think there is any better way to execute this?

As always thanks in advance for the help!
Krish

Was this reply helpful? Yes No
KRider 577 Super User 2024 Season 1 on 13 Nov 2023 at 11:18:09

Like (0)

Report

Re: Can I export data from Power BI to SharePoint List?

Hey Krish,

syntax

Here is what it's saying doesn't exist(just too many ' < of these)

'items('Apply_to_each')['Checklist''[User_ID]']'

Error Details can provide great feedback if you know what to look for. Schema/coding is all about syntax.

Here is the line that works:

'items('Apply_to_each')['Checklist[User_ID]']'

Was this reply helpful? Yes No
yadukrishnankv9 12 on 10 Nov 2023 at 20:18:00

Like (0)

Report

Re: Can I export data from Power BI to SharePoint List?

Thank You @KRider for the quick response!

I succeeded in writing the JSON Schema, thank you so much!

However, the next and last step seems to give trouble now:

Error Details: Unable to process template language expressions in action 'Create_item' inputs at line '0' and column '0': 'The template language expression 'items('Apply_to_each')['Checklist''[User_ID]']' cannot be evaluated because property 'Checklist'[User_ID]' doesn't exist, available properties are 'Checklist[User_ID], Checklist[Display_name], Checklist[Manager], Checklist[DaysIn], Checklist[Owner], Checklist[Task]'. Please see https://aka.ms/logicexpressions for usage details.'.

Any direction as to what this error could be?

Thank you for your help!!
Krish

Was this reply helpful? Yes No
KRider 577 Super User 2024 Season 1 on 10 Nov 2023 at 19:36:15

Like (1)

Report
Re: Can I export data from Power BI to SharePoint List?

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]",

Was this reply helpful? Yes No