Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

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.

5 REPLIES 5
v-yulgu-msft
Microsoft Employee
Microsoft Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ImkeF
Community Champion
Community Champion

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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@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
Not applicable

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?

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.