Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Got it to work in the end with multiple parameters and a query. Full example below. Thanks goes to @ pkoetzing for his post
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"
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
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
Got it to work in the end with multiple parameters and a query. Full example below. Thanks goes to @ pkoetzing for his post
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"
Got it to work in the end with multiple parameters and a query. Full example below. Thanks goes to @pkoetzing for his post
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |