Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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.
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
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
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
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.
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?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
74 | |
57 | |
39 | |
33 |
User | Count |
---|---|
71 | |
66 | |
57 | |
49 | |
47 |