Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hey Power BI Community!
I have a pretty interesting challenge that I have not been able to figure out. My main question is whether you can create a method for Power Query to automatically create queries? The bigger problem that I have is that I have a function that I need to pass while I do this. I will post the M Code for my Function and Final Queries below. Up to this point I have used this same function method for a couple of different projects, but this is the first time I have more than 15 queries and paths to pass through the function.
My goal is for my function to iterate through each of the rows in the [Path] column on my #"Posts-Export-2024-February-07-1913-1" table.
I have created the table #"Function" that goes ad grabs the information I want from the respective web link and row.
Up to this point everything is working. But what I want to happen is to create another way to have power query autmatically create the end query and have a new query for each separate link from the #"Posts-Export-2024-February-07-1913-1" table. This may mean that there needs to be a way to change the Row value in the final query, but I can't quite figure it out.
Important Note: I need the final queries to stay in the text format. When the queries are converted to a table or a list I do not get all of the information I need from the web link.
Any help on how to solve this issue would be apprecited! I think that a method for doing this will be very useful because you could be able to change the datasource to iterate throguh binary, json, web, and most other connectors.
Function Query M Code:
let
Source = (PathName as text) => let
Source = Web.BrowserContents(PathName as text),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "BODY"}}),
#"Extracted Text Between Delimiters" = Table.TransformColumns(#"Extracted Table From Html", {{"Column1", each Text.BetweenDelimiters(_, "By:", "Comments"), type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Extracted Text Between Delimiters","dataset","semantic model",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Dataset","semantic model",Replacer.ReplaceText,{"Column1"}),
Column1 = #"Replaced Value1"[Column1],
#"Next Step" = #"Replaced Value"[Column1]{0}
in
#"Next Step"
in
Source
Final Query M Code:
let
Row = 0,
//Rows start at 0, not at 1
Source = #"Function"(#"Posts-Export-2024-February-07-1913-1"[Path]{Row}),
Result = if #"Posts-Export-2024-February-07-1913-1"[Path]{Row} <> "" then Source else ""
in
Result
Hi @d_rohlfs
That’s a great idea, but so far it seems that automatically creating queries in Power Query is not yet possible. Or maybe it can be done, but above my experience. Looking forward to smart ideas and solutions for this from other individuals!
Best Regards,
Jing