Forum Discussion
Variable parameter based on periods
I have a set of 12 exact same excel files (one for each 12 previous months).
I want to append them in one single historical source in Power BI.
As I have a big set of data I cannot append the data in a consolidated excel file (would be too big and too slow to manage).
Hence I would like to create a dynamic parameter to query all 12 files having a name based on that parameter.
For example:
I have file_201706.xlsx, file_201705.xlsx, etc...
I would like to set a parameter being my max report date: parameter = 30/06/2017.
I want each connection to be refreshed using something like:
File1 source = "http://....file1_" & year(parameter) & month(parameter) & ".xlsx"
File2 source = "http://....file2_" & year(parameter) & month(parameter) -1 & ".xlsx"
Etc...
Is what I am looking for feasible?
Is there a better way to proceed?
Thank you all in advance
I have test it on my local environment, everything works fine.
You can create a text parameter like 201706, 201707, 201708.... and then use it in you query like below.
let
Source = Excel.Workbook(File.Contents("C:\Users\v-caliao\Desktop\SampleDataSource\" & #"Parameter" & ".xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Type", type text}, {"Sales", Int64.Type}})
in
#"Changed Type"Reference
http://www.fourmoo.com/2016/11/23/power-bi-using-parameters-for-flat-file-csv-excel-sources/Regards,
Charlie Liao
2 Replies
I have test it on my local environment, everything works fine.
You can create a text parameter like 201706, 201707, 201708.... and then use it in you query like below.
let
Source = Excel.Workbook(File.Contents("C:\Users\v-caliao\Desktop\SampleDataSource\" & #"Parameter" & ".xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Type", type text}, {"Sales", Int64.Type}})
in
#"Changed Type"Reference
http://www.fourmoo.com/2016/11/23/power-bi-using-parameters-for-flat-file-csv-excel-sources/Regards,
Charlie Liao
Thank you.
I figured out that I was also able to store all monthly files into a single folder.
I am not yet very familiar with parameters and functions but it was quite easy to understand I just connected to a Sharepoint site where the files are located and filtered on the folder path using a parameter MyFolder
let
Source = SharePoint.Files("https://MyCompany/sites/", [ApiVersion = 15]),
#"Filter rows" = Table.SelectRows(Source, each [Folder Path] = #"MyFolder").I have just asked Power BI to combine the files which created all functions and parameters.
So now I better understand how to writte such connections.
Thanks a lot for your help
