Advanced Editor data source variable/parameter issue

[Jeffery24]

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.

[v-frfei-msft]

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]

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 · Accepted solution]

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...  

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"

[Jeffery24]

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...

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"