cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mphillenga
Frequent Visitor

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

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

1 ACCEPTED SOLUTION
mahoneypat
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

8 REPLIES 8
mahoneypat
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


mphillenga
Frequent Visitor

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
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

mahoneypat
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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
Super User IV
Super User IV

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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,

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!!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.