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")),
    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}}),

I will be doing the same for a number of different sources and then combining them all together on one table. The issue that I am facing is that I need to distinguish between each data set that is pulled. So what I need to do is add a column to this part of the query and put in a value that distinguishes this data set query from the others. What I would like to do is take the end of the URL token, "arenano01", and assign it as a variable and then insert this variable into a created column. So then this created column will have the dynamic variable for each respective data set.

 

Is this possible in Power BI Query Editor?

 

If not, I think I will have to resort to pulling in the data via VBA.

 

Thanks.

Need Help

5 Replies

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