cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Jeffery24 Regular Visitor
Regular Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Jeffery24 Regular Visitor
Regular Visitor

Re: Advanced Editor data source variable/parameter issue

 

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"

 

4 REPLIES 4
Community Support Team
Community Support Team

Re: Advanced Editor data source variable/parameter issue

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jeffery24 Regular Visitor
Regular Visitor

Re: Advanced Editor data source variable/parameter issue

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 Regular Visitor
Regular Visitor

Re: Advanced Editor data source variable/parameter issue

 

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 Regular Visitor
Regular Visitor

Re: Advanced Editor data source variable/parameter issue

 

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"