Report on Sharepoint Lists in Sharepoint Template

[nesv]

Hi,

I want to create a Sharepoint Template with integrated Power BI Reports, based on Lists in the actual Site.

I've managed to create the template, but when I create a new site, based on this template, I need to do the following.

Open the .pbix report in Power BI Desktop.

Change the datasource.

Publish the report to the new new site.

Change the embed-link in the web-parts.

I hope there is a easier solution.

Any suggestions are appreciated.

Thannks in advance

Svenson

[v-yuezhe-msft]

@nesv,

From Power BI side, to simplify the process, you can create data source parameter in your PBIX file following the guide in the blog below. This way, you can choose different source each time and when you publish the PBIX file to Power BI Service, the embed link is always the same.

http://biinsight.com/power-bi-desktop-query-parameters-part-1/

Regards,
Lydia

[nesv]

@v-yuezhe-msft,

The parameters doesn't solve the data source problem.

I can change the source, but I still have to change the ID for the List-table, in Advanced Editor

[v-yuezhe-msft]

@nesv,

How about you create parameter for ID in Advanced Editor?

Regards,
Lydia

[nesv]

@v-yuezhe-msft

I've googled for a while, and can't find any guide in getting the ID of a Sharepoint List.

My mission is to create a Parameter (or a Query), that gets the ID of my List.

Then I would be able to use this parameter later on 🙂

Do you have any guide (or hint) that could lead me in the right direction?

regards Svenson

[v-yuezhe-msft]

@nesv,

On Power BI side, you can add a new blank query and paste the following code to the Advanced Editor of the blank query.

let 
    parameterSource = (IDparameter as text) => 

let
    Source = SharePoint.Tables("https://sharepointsite", [ApiVersion = 15]),
    #"getlist" = Source{[Id=""&IDparameter&""]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"getlist",{{"ID", "ID.1"}})
in
    #"Renamed Columns"

in
   parameterSource

Then manually enter ID in the function to get a table of that ID. 
/

It is not possible to change ID when entering sharepoint site URL at the beginning.



Regards,
Lydia

[nesv]

@v-yuezhe-msft

What I was looking for, was to automate the process, when creating a new SharePoint Site based on a template.

I wanted to create the PowerBI in a way, that the user only have to enter the sitename, and some query and parameters could take care of the rest 🙂

I know the name of the 2 SharePoint lists I want to make PowerBI reports on.

But as far as I can see, for now, it isn't possible.

I thank you very much for your answers, but will not mark this as a solution 🙂

Thanks

Svenson