Forum Discussion

Jeffery24's avatar
Jeffery24
Helper I
7 years ago
Solved

Advanced Editor data source variable/parameter issue

Hi,

My dashboard has varying data source and I was following the http://datachant.com/2016/04/29/power-bi-templates/ setup up a text parameter to change the source name based on user input. The end goal is to create a template just as in the blog.

 

The parameter "Account Name" returns the name of the customer to look up the "customer container type" file  and then returns the correct source file from Sharepoint. The below code works when only using the parameter once, see red text for working parameter part.

 

let 
Source = SharePoint.Files("https://dcompany.sharepoint.com/sites/KPIDashboard", [ApiVersion = 15]),
    #"customerx Container Type xlsx_https://dcompany sharepoint com/sites/KPIDashboard/Shared Documents/SBI KPI/Email Messages/" = Source{[Name=#"Account Name" & " Container Type.xlsx",#"Folder Path"="https://dcompany.sharepoint.com/sites/KPIDashboard/Shared Documents/SBI KPI/Email Messages/"]}[Content],
    #"Imported Excel" = Excel.Workbook(#"customerx Container Type xlsx_https://dcompany sharepoint com/sites/KPIDashboard/Shared Documents/SBI KPI/Email Messages/"),
    Sheet1_Sheet = #"Imported Excel"{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Container Type", type text}, {"Referance", type text}, {"Max Fill", Int64.Type}, {"Max Weight", Int64.Type}, {"TEU", type number}, {"Target Fill", Percentage.Type}})
in
    #"Changed Type"

But when I use the same logic in red for the parameter value to replace the rest of the editor, i.e. the parts in blue, it errors the upload.

 

let 
Source = SharePoint.Files("https://dcompany.sharepoint.com/sites/KPIDashboard", [ApiVersion = 15]),
    # & #"Account Name" & " Container Type xlsx_https://dcompany sharepoint com/sites/KPIDashboard/Shared Documents/SBI KPI/Email Messages/" = Source{[Name=#"Account Name" & " Container Type.xlsx",#"Folder Path"="https://dcompany.sharepoint.com/sites/KPIDashboard/Shared Documents/SBI KPI/Email Messages/"]}[Content],
    #"Imported Excel" = Excel.Workbook(# & #"Account Name" & " Container Type xlsx_https://dcompany sharepoint com/sites/KPIDashboard/Shared Documents/SBI KPI/Email Messages/"),
    Sheet1_Sheet = #"Imported Excel"{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Container Type", type text}, {"Referance", type text}, {"Max Fill", Int64.Type}, {"Max Weight", Int64.Type}, {"TEU", type number}, {"Target Fill", Percentage.Type}})
in
    #"Changed Type"

Any advice would be appreciated. Thanks.

Need Help
  • Jeffery24's avatar
    Jeffery24
    7 years ago

     

    Got it to work in the end with multiple parameters and a query. Full example below. Thanks goes to @ pkoetzing for his post

    https://community.powerbi.com/t5/Integrations-with-Files-and/How-to-switch-sources-within-sharepoint-folder/m-p/506186#M19505  

     

    let 
    Source = SharePoint.Files(SourceID, [ApiVersion = 15]),
    FileID = Source{[Name=MyFileParameter&".xlsx",#"Folder Path"=MyPathParameter]}[Content],
    #"Imported Excel" = Excel.Workbook(FileID),
    Sheet1_Sheet = #"Imported Excel"{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Container Type", type text}, {"Referance", type text}, {"Max Fill", Int64.Type}, {"Max Weight", Int64.Type}, {"TEU", type number}, {"Target Fill", Percentage.Type}})
in
    #"Changed Type"

     

4 Replies

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

    Hi Jeffery24,

     

    Based on my test, we cannot set parameter of step name here. So I guess the issue shoule be related to this. Why did you want to do like this?

     

    Regards,

    Frank

    • Jeffery24's avatar
      Jeffery24
      Helper I
      7 years ago

      Hi Frank,

       

      Thanks for testing. The only reason I used the format above is that's how it comes out in the editor after I manually add the stages. Is there another way so I can include a variable instead of "Customerx"?

      I just need to import the variable source file from Sharepoint and then add some header formatting.

       

      Thanks. Tom

      • Jeffery24's avatar
        Jeffery24
        Helper I
        7 years ago

         

        Got it to work in the end with multiple parameters and a query. Full example below. Thanks goes to @pkoetzing for his post

        https://community.powerbi.com/t5/Integrations-with-Files-and/How-to-switch-sources-within-sharepoint-folder/m-p/506186#M19505

         

         

         

        let 
    Source = SharePoint.Files(SourceID, [ApiVersion = 15]),
    FileID = Source{[Name=MyFileParameter&".xlsx",#"Folder Path"=MyPathParameter]}[Content],
    #"Imported Excel" = Excel.Workbook(FileID),
    Sheet1_Sheet = #"Imported Excel"{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Container Type", type text}, {"Referance", type text}, {"Max Fill", Int64.Type}, {"Max Weight", Int64.Type}, {"TEU", type number}, {"Target Fill", Percentage.Type}})
in
    #"Changed Type"