Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So I have the following code to pull in a table from a website:
let Source = Web.Page(Web.Contents("https://www.baseball-reference.com/play-index/batter_vs_pitcher.cgi?batter=arenano01")), Data1 = Source{1}[Data], #"Changed Type" = 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}}) in #"Changed Type"
If I wanted to do the same thing for say 20-30 exactly similar webpages but they have different data, how could I do this on one query and append the results together rather than having 30 different queries all doing the same thing.
Is it possible to do this in Power BI or should I resort to my VBA skills to pull in all te data into Excel?
Solved! Go to Solution.
Hi nobodyukno,
To achieve your requirement, you can use Table.Combine instead.
#"TableCombined" = Table.Combine({#"Changed Type1", #"Changed Type2"})
You can refer to PBIX here:
https://www.dropbox.com/s/hkv1dspegfp87sx/For%20nobodyukno.pbix?dl=0
Regards,
Jimmy Tao
I wrote a blog article a long time ago called "Merge Queries with M" or something like that. Essentially, you just need to do something like:
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}}) Source2 = Web.Page(Web.Contents("https://www.baseball-reference.com/play-index/batter_vs_pitcher.cgi?batter=arenano01")), Data2 = Source2{1}[Data], #"Changed Type2" = Table.TransformColumnTypes(Data2,{{"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}}) #"TableAppend" = Table.Append(Source1, Source2) in #"TableAppend"
Thanks for the reply. I get an error stating that the name "Table.Append" was not recognized. Excuse my ignornace but I'm not too familiar with M code.
Thanks
Hi nobodyukno,
To achieve your requirement, you can use Table.Combine instead.
#"TableCombined" = Table.Combine({#"Changed Type1", #"Changed Type2"})
You can refer to PBIX here:
https://www.dropbox.com/s/hkv1dspegfp87sx/For%20nobodyukno.pbix?dl=0
Regards,
Jimmy Tao
Thank you that worked!
I have another M coding question as I'm used to VBA where I can declare variables.
Now that with the above code I can bring multiple sets of data into one table, is there anyway I can distinguish the sets of data by adding a column? Is it possible to add a column to each query and have that column enter a variable from the code above (at worst enter a sequential number and I can setup mapping to further identify)? If it's possible to assign a variable in M code, then I would want to set the variable up as the last bit of text from the URL, so "arenano" in the example above. Then it would put that variable in a new column for each query and then combine together at the end with the one column havin the identifer for each query pull.
Thanks again for the help.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |