Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Assign a variable within Query Editor?

So I have the following piece of code in my query editor:   let Source1 = Web.Page(Web.Contents("https://www.baseball-reference.com/play-index/batter_vs_pitcher.cgi?batter=arenano01")), Dat...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi Anonymous,

 

Based on my research, I didn't find a valid solution to achieve this goal. 

 

Hi,   MarcelBeug, ImkeF, do you have any ideas? Appreciate for your help if you would please share some suggestions.

 

Regards,

Yuliana Gu

  • ImkeF's avatar
    ImkeF
    Community Champion
    8 years ago

    I will publish a blogpost about it later during the day and post a link here.

    • ImkeF's avatar
      ImkeF
      Community Champion
      8 years ago

      Hi Anonymous,

      you can create a function like this that you apply in a Table.AddColumn-step, referencing a column "ColumnVariable" with the variable values:

      (MyVariable as text) =>
      let    
      Source1 = Web.Page(Web.Contents("https://www.baseball-reference.com/play-index/batter_vs_pitcher.cgi?batter=arenano01",
                            [Query = [batter = MyVariable]] )),    
      Data1 = Source1{1}[Data],    
      #"Changed Type1" = Table.TransformColumnTypes(Data1,{{"Name", type text}, {"PA", Int64.Type}, {"AB", Int64.Type}, {"H", Int64.Type}, {"2B", Int64.Type}, {"3B", Int64.Type}, {"HR", Int64.Type}, {"RBI", Int64.Type}, {"BB", Int64.Type}, {"SO", Int64.Type}, {"BA", type number}, {"OBP", type number}, {"SLG", type number}, {"OPS", type number}, {"SH", Int64.Type}, {"SF", Int64.Type}, {"IBB", Int64.Type}, {"HBP", Int64.Type}, {"GDP", Int64.Type}, {"missG", type text}}),

      This syntax with the separate Query-record ensures that your query will be refreshable in PBI service.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        ImkeF

         

        Thank you for the reply. As I have not used custom functions before in Power BI, could you explain a little bit more?

         

        So the code you provided would be it's own stand alone function? 

         

        Then in my actual query, I would add a step to "Add Column - Invoke Custom Function" and then reference my function? 

         

        I can't seem to tell if the function is returning anything as a variable.