Assign a variable within Query Editor?

[Anonymous]

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.

[v-yulgu-msft]

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]

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

[ImkeF]

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]

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

[Anonymous]

So after looking into Custom Functions some more I think I have a little bit of a better understanding. However, I don't think the above code is assigning anything as a variable?