POWER BI TEMPLATE DATA SOURCE ISSUES

[Anonymous]

Hi team,

I am trying to create a template to use for multiple clients, the datasets are stored in microsoft sql server studio. 

Process:

1. Importing the first dataset from SQL into power bi, transforming the dataset and then creating a report out of it.

2.I am able to export the original report and create a fresh template file(.pbit)

3.When the Template file is opened, the dashboard looks exactly the same as the orginal report, with the dataset and respective columns on the right hand side present.

4. When attempting to change the data source (to create a report for a differerent client) , I am able to successfully connect to a different sql database (different client but same sql table structure and values). However when the changes are applied, power bi responds with:

[The original table used to create the template] - The key didn't match any rows in the table.

Am i going about this process correctly? 

cheers.

[fz1009]

I think first of all,

you may not created the file location as a parameter.

You need to first create a parameter, then connect the paramater to the power BI visuals.

Then when you open the template, it will prompt you to fill in file location. From here, you may type in the new location and file name. In this step, I found that if we forget to put suffix of data name such as xlsm or csv, you will get the same error message as what you got.

[v-stephen-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.

Best Regards,
Stephen Tao

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Power BI report templates contain the following information from the report from which they were generated:

• Report pages, visuals, and other visual elements
• The data model definition, including the schema, relationships, measures, and other model definition artifacts
• All query definitions, such as queries, Query Parameters, and other query elements

What is not included in templates is the report's data.

Report templates use the file extension .PBIT (compare to Power BI Desktop reports, which use the .PBIX extension).

For the problem you encountered, I found these similar posts for your reference:

how to resolve Power BI error - the key didn't match any rows in the table
Expression.Error: The key didn't match any rows in the table
Expression Error.: The key didn't match any rows in the table

For more information, please kindly refer to:

Create report templates for Power BI Desktop

Power BI Templates

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AllisonKennedy]

@Anonymous 

Just to sort of summarize what we did in case others need it: 

We created a parameter to reference the client name, and updated the Navigation step of the Query to use that parameter. 

This blog helps explain basics for using parameters Use Parameters to Combine Data 

Here is the M query for Custom Function using Client name as parameter, assuming the databaseName = ClientName and tableName = ClientNametable

let
Source = (ClientParameter as text) => let
Source = Sql.Databases("ServerName.database.windows.net"),
#"Filtered Rows" = Table.SelectRows(Source, each [Name]=ClientParameter),
#"Select Database" = #"Filtered Rows"{[Name=ClientParameter]}[Data],
#"Select Table" = ClientParameter{[Schema="dbo",Item=ClientParameter&"table"]}[Data]
in
#"Select Table"
in
Source

[Anonymous]

I should also point out that the dataset that I have attempted to bring into the template isnt viewable in power bi, the prompt 'The key didnt match any rows in the table' is as far as i can go. 

[AllisonKennedy]

@Anonymous  This means that you have used the name of the table explicitly in your M code. If you're comfortable pasting the M query here to share, I can point you to the exact step, but if you look at the 'Navigation' step, this will likely be where your issue is. Is the table name different for each client? 

[Anonymous]

Hey Allison thank you for following up so quickly. 

The data is quite sensitive so i cant share any related queries.

There is a database for each client in the sql server, and each client database has a single table with the same structure. However I have transformed the initial report data once I imported it into POWER BI.