one model to manage different data source format

[Anonymous]

Hello,

I am wondering on the best method to use on the following issue :

• I receive different excel fact file from various source, basically most of those file have the same information : date, localization, … stuff like that, but the files have different formats : column name and column title
• I have established to a standard power bi model to analyze those files, with the KPI I want
• I would like to be able to reuse the model to visualize the with the minimum of effort, and by someone with the minimum of technical knowledge

I am wondering what would be the best way to implement that. I have considered the following options :

• Before data loading : have an excel query that would transform my data into some kind of meta model before loading the data
  • I think it might require a bit of technical expertise to do so
• On loading the data : have parameters that would tell the loading scripts which column name to use
  • It might works ok if there is few parameters but it won’t be possible if there is too many of them
• After loading : create a virtual table that would map the meta column name with the raw data
  • Probably the best option

I would hope someone could point me the right direction

 thanks 

g

[Anonymous · Accepted solution]

Hi @Anonymous，

You can create parameters in your Desktop file to define which excel file(contains specific column name and column title) to connect to, then create reports in  the Power BI Desktop file.

And in order for other users to  use the model in your Power BI Desktop file,  you can create a Power BI Report Template(PBIT file) , the Power BI Report Template contains the definition of the Report (pages, visuals, etc.), Data Model definition (schema, relationships, measures, etc.) and Queries definition (collection of queries, including Query Parameters, etc.). Other users can instantiate your template by double-clicking on the PBIT file, or by using the “File –> Import –> Power BI Template” path.

For more details, please review this blog: https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/

Thanks,
Lydia Zhang

[Anonymous]

Right direction indeed, Thanks a lot @Anonymous