Forum Discussion

vjnvinod's avatar
vjnvinod
Impactful Individual
8 years ago
Solved

Power query limitation

Hi,

 

I was going through a challenge in connecting dynamic excel names into power Bi (Power query)

lets say i connected

 

Global Media and Entertainment Account Listing FY18 P09.xls

Global Media and Entertainment Account Listing FY18 P12.xls

 

nex time my file name changes to

Global Media and Entertainment Account Listing FY19 P01.xls

Global Media and Entertainment Account Listing FY19 P02.xls

 

in this case my refresh option fails and i need to reconnect to P01 and P02.

How do i achieve this, without reconnecting the source file again and again?

Need Help
Show and Tell
  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    Hi vjnvinod,

     

    If your file names have been changed, here I suggest you to work with parameter. Also a made a test, please refer to the following steps.

     

    1. Get data from the folder, in the edit query, we can create a parameter like this.

     

     

    2.After that we can edit the code in the Advanced Editor, then we can filter the file name based on the parameter. Next time, once file name changed, you can choose the file names that you want.

     

    let
    Source = Excel.Workbook(File.Contents("file path\" & #"Parameter1"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"id", Int64.Type}, {"name", type text}})
in
#"Changed Type"

     

    Regards,

    Frank

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    8 years ago

    Hi vjnvinod,

     

    If your file names have been changed, here I suggest you to work with parameter. Also a made a test, please refer to the following steps.

     

    1. Get data from the folder, in the edit query, we can create a parameter like this.

     

     

    2.After that we can edit the code in the Advanced Editor, then we can filter the file name based on the parameter. Next time, once file name changed, you can choose the file names that you want.

     

    let
    Source = Excel.Workbook(File.Contents("file path\" & #"Parameter1"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"id", Int64.Type}, {"name", type text}})
in
#"Changed Type"

     

    Regards,

    Frank

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      8 years ago

      Hi vjnvinod,

       

      Does that make sense? If so , kindly mark the answer as a solution to close the case, thanks in advance.

       

      Regards,

      Frank