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"

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

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

       

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