One template (containing query steps, chart formatting, etc) with varying data sources

[mphillenga]

Hello,

I'm quite new to PowerBI and want to create a template that allows me to re-use graphs, charts and queries for different datasets. I have various viewers that should have their own report (based on the template with exactly the same charts/queries/etc) but loaded with different data, published in their own workspace or app. So the data sources are different, but everything I've done in PowerBI Desktop and exported as a template should be the same.

- I have multiple data sources, named dataset_supplier1_A, dataset_supplier1_B, dataset_supplier1_C, etc. They are ingested from Google Big Query and then 'import'

- The column names are exactly the same for each dataset. E.g. dataset_supplier1_A has the same column names as dataset_supplier2_A, and dataset_supplier1_B has the same column names as dataset_supplier2_B. The only difference is the name of the supplier, referenced in the table name

- I've also done several transformations (format currency/ date), created measures, some DAX formulas & calculated columns, etc. I would like to keep those and re-use for another supplier version of the report through the template so that I don't have to re-do them

- I've also done a lot of designing of the charts. I would like to re-use those through the template as well so that I don't have to re-do them

Currently I delete all datasets from supplier1 and export the report as a template. When opening and ingesting datasets from supplier2, I have to manually fill the fields & values of all the charts, do all the transformations/calculated columns and do the designing of the charts all over again.

I looked at 'roles' but then I would need to merge the data from all suppliers in a dataset in Google Big Query, giving a supplier viewer access to specific data through a certain role. Due to privacy and other reasons I don't want to set things up that way.

Furthermore I've read a bit about 'manage parameters' and 'data flows', but I haven't figured out what the best way is to set this template up.

Hopefully somebody can point me into the right direction.

Thanks

[mahoneypat]

I haven't confirmed all those points, but believe the template file should retain all those things.  If you had measures/columns on tables you deleted, those would be lost of course.  Have you tested adding a column, measure formatting since you've created your new table/query and saving as a template to confirm?

Regards,

Pat

[mphillenga]

Thanks a lot @mahoneypat .

The parameter setup works. I have made a list of the suppliers and assume that when there is new supplier you can add it to the parameter list through the 'edit' function.

The fields from each table is loaded into every chart. That goes well, thanks a lot for the solution.

However, I run into the following:

• Created measures and calculated columns are not generated. Perhaps this is due to deleting all the tables (from a supplier), before saving it to the template format. I understood templates would be able to store any measures, calculated columns, queries, etc. Not sure how to overcome that? If I keep the tables in the template file, I have troubles loading through the parameter if it's a different supplier.
• The same applies to: no formating of data (e.g. currency, date/time) and the applied steps in the queries are not executed/ present
• Tables/queries with custom sorting where an additional column has a relationship with a custom sorting table that is saved within the template file

Hope you can help to clarify the above.

Thanks a lot for your help.

Best,

•  

[mahoneypat · Accepted solution]

You should be able to make a parameter called SupplierName and use it in your query like this (just first part shown).  Key text is in red and should not have quotes around it.  With a similar approach, you could also make that a table of all your supplier names, and pass that supplier name into this query, and then combine all the data from all the suppliers (but I know you were looking for an individual supplier solution on this one).

let
Source = GoogleBigQuery.Database(null),
#"ourcompanyname-projectname" = Source{[Name="ourcompanyname-projectname"]}[Data],
supplier_name1_Schema = #"ourcompanyname-projectname"{[Name=SupplierName,Kind="Schema"]}[Data],
supplier_name1_master_table_Table = supplier_name1_Schema{[Name=SupplierName & "_master_table",Kind="Table"]}[Data],

Regards,

Pat

[mahoneypat]

Please go to the Advanced Editor for the query for one of the dataset and paste that M code here.  We can then suggest modified M code that will reference a dataset name parameter.  Once you have that, you can save it as a template file (pbit) and use that to quickly switch datasets in the report.  If you plan to have multiple parallel reports published, you'll need to upkeep those all separately if you make changes.  Another option would be to use Paginated Reports (premium per user is free currently).  With that you could set up subscriptions to each supplier (based on a parameter) emailed to them at your set frequency.  Those reports would be static (pdf, PowerPoint), but you would only have to manage one solution.

Regards,

Pat

[mphillenga]

Thanks a lot @mahoneypat .

Please find below the M code for two different queries from the same supplier.

The companyname-projectname is fixed for all suppliers.

The supplier_name1 is the only thing that changes for every supplier. As said all code, designing of charts, etc should be exactly the same.

Query 1 M code:

let
Source = GoogleBigQuery.Database(null),
#"ourcompanyname-projectname" = Source{[Name="ourcompanyname-projectname"]}[Data],
supplier_name1_Schema = #"ourcompanyname-projectname"{[Name="supplier_name1",Kind="Schema"]}[Data],
supplier_name1_master_table_Table = supplier_name1_Schema{[Name="supplier_name1_master_table",Kind="Table"]}[Data],
#"Replaced Value" = Table.ReplaceValue(master_table_Table,"1","Very low",Replacer.ReplaceText,{"r_quintile"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","2","Low",Replacer.ReplaceText,{"r_quintile"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","3","Medium",Replacer.ReplaceText,{"r_quintile"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","4","High",Replacer.ReplaceText,{"r_quintile"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","5","Very high",Replacer.ReplaceText,{"r_quintile"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","5","Very high",Replacer.ReplaceText,{"f_segment"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","4","High",Replacer.ReplaceText,{"f_segment"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","3","Medium",Replacer.ReplaceText,{"f_segment"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","2","Low",Replacer.ReplaceText,{"f_segment"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","1","Very low",Replacer.ReplaceText,{"f_segment"}),
#"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","1","Very low",Replacer.ReplaceText,{"m_quintile"}),
#"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","2","Low",Replacer.ReplaceText,{"m_quintile"}),
#"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","3","Medium",Replacer.ReplaceText,{"m_quintile"}),
#"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12","4","High",Replacer.ReplaceText,{"m_quintile"}),
#"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13","5","Very high",Replacer.ReplaceText,{"m_quintile"})
in
#"Replaced Value14"

Query 2 M code:

let
Source = GoogleBigQuery.Database(null),
#"ourcompanyname-projectname" = Source{[Name="ourcompanyname-projectname"]}[Data],
supplier_name1_Schema = #"ourcompanyname-projectname"{[Name="supplier_name1",Kind="Schema"]}[Data],
supplier_name1_simple_rfm_segments_Table = supplier_name1_Schema{[Name="supplier_name1_simple_rfm_segments",Kind="Table"]}[Data],
#"Added Index" = Table.AddIndexColumn(supplier_name1_simple_rfm_segments_Table, "Index", 1, 1, Int64.Type),
#"Replaced Value" = Table.ReplaceValue(#"Added Index",10,40,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",11,120,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",12,80,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",13,60,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3",9,20,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4",8,10,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5",7,13,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6",6,50,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7",5,7,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8",4,11,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9",1,9,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10",2,1,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11",20,2,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12",40,4,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13",50,5,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value15" = Table.ReplaceValue(#"Replaced Value14",60,6,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value16" = Table.ReplaceValue(#"Replaced Value15",80,8,Replacer.ReplaceValue,{"Index"}),
#"Replaced Value17" = Table.ReplaceValue(#"Replaced Value16",120,12,Replacer.ReplaceValue,{"Index"}),
#"Sorted Rows" = Table.Sort(#"Replaced Value17",{{"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
#"Removed Columns"

Thanks a lot for your help.

[mahoneypat]

This sounds like a good use of template files.  Can you show two of your queries (or mock ones if needed, one for dataset1 and one for dataset2) to see how they differ, so a specific use of parameters can be suggested?  Also, here is an article I wrote about template files, in case it is helpful.

Template Files are an Under-Sung Feature – Hoosier BI

Regards,

Pat

[mphillenga]

Hi Pat,

Thanks a lot for your reponse and sharing the article. Very insightful and it shows that templates should be able to do the job. The queries (column names and applied steps) for dataset 1 and dataset 2 are exactly the same. 

I'm not sure if I understood your question, so please find some screenshots below of:

• Query column names example A (similar for dataset 1 and dataset 2)
• Query column names example B (similar for dataset 1 and dataset 2)
• Naming of a table of dataset 1 and dataset 2
• Applied query steps
• Calculated column

Thanks,

[mphillenga]

Apologies @mahoneypat for (again) a late response. But I managed to find time during the Christmas holiday to properly work on the feedback you provided.

Everything seems to be working as expected. Thanks a lot for the support!!