Forum Discussion

Mbechet's avatar
Mbechet
Frequent Visitor
9 years ago
Solved

Variable parameter based on periods

Hi everyone, 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 app...
Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    Mbechet,

     

    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

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
9 years ago

Mbechet,

 

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

  • Mbechet's avatar
    Mbechet
    Frequent Visitor
    9 years ago

    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