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

     

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"