Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jeffery24
Helper I
Helper I

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

 

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"

 

View solution in original post

4 REPLIES 4
v-frfei-msft
Community Support
Community Support

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 others find it more quickly.

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

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"

 

 

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"

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.