Forum Discussion
Power Query Source - SSIS with function not working
Hello,
I have made a model in Power BI desktop with a Query(Y) that is partly dependend on Function(X).
Background: it is a JSON(web) link, which needs to loop through multiple pages to get all data.
This is working perfectly in PowerBI desktop.
Since the datasource is based on a website, I want to make a loader in SSIS and using the "Power Query Source" (PQS) of SSIS. However, when using the PQS in SSIS I can only input one "single query text". I am not able to input the Function and therefore the PQS in SSIS is not working.
Could you please guide me how to include the Function X and Query Y in one "single query text" so I can make it work.
I have followed the following links:
@https://www.mssqltips.com/sqlservertip/6305/power-query-source-for-sql-server-integration-services/
@https://www.sqlservercentral.com/articles/create-custom-functions-in-power-query
However, these solutions are all related to single query sources.
This is my sample adjusted code:
Function1 fxSample:
(PageStart as text)=>
let
Source = Json.Document(Web.Contents("https://mywebsite?{}&pagina="&PageStart&"")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded result" = Table.ExpandListColumn(#"Converted to Table", "result"),
#"Expanded result1" = Table.ExpandRecordColumn(#"Expanded result", "result", {"VARIABLES"}),
#"Expanded result.estado" = Table.ExpandRecordColumn(#variables....
#"Changed Type"
Query1:
let
Source = {1..10000},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Structure),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Pages"}, {"Pages"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Pages", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if [Column1] <= [Pages] then "x" else null),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = "x")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Pages"}),
#"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "fxSample", each fxSample([Column1])),
#"Expanded fxSample" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxSample", {"result.zag.nr"}, {"fxSample.result.zag.nr"})
in
#"Expanded fxSample"
Could somebody guide me in the right direction, how to combine this in SSIS or to combine fxSample & Query1 in one single query?
Thank you!
3 Replies
v-jingzhangCommunity Support
Hi DieZarAx64
You can include the Function X in the Query Y. Just like below. Don't forget to add a comma at the end of the function code part and change duplicated step names if any.
let /* ---------- fxSample function codes start ---------- */ fxSample = (PageStart as text)=> let fxSource = Json.Document(Web.Contents("https://mywebsite?{}&pagina="&PageStart&"")), #"Converted to Table" = Table.FromRecords({fxSource}), #"Expanded result" = Table.ExpandListColumn(#"Converted to Table", "result"), #"Expanded result1" = Table.ExpandRecordColumn(#"Expanded result", "result", {"VARIABLES"}), #"Expanded result.estado" = Table.ExpandRecordColumn(#variables.... in #"Changed Type", /* ---------- fxSample function codes end ---------- */ Source = {1..10000}, #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Structure), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Pages"}, {"Pages"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Pages", type text}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if [Column1] <= [Pages] then "x" else null), #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = "x")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Pages"}), #"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "fxSample", each fxSample([Column1])), #"Expanded fxSample" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxSample", {"result.zag.nr"}, {"fxSample.result.zag.nr"}) in #"Expanded fxSample"Kindly let me know if this works or not.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.Thank you for your reaction.
I am able now to put both queries in "one single query", however SSIS does not recognize the source.
In SSIS you need to make a new connection*, I have tried the following inputs:
https://mywebsite?{}&pagina="&PageStart&"https://mywebsite?{}When I hit the button "Select Data Source" it does not recognize any datasource from the query.
And when I mannualy select it from the connection manager, it also dont work.
How should we define the datasource?
keobrieHelper I
Was this issue ever resolved? I'm having a similar issue attempting to import multiple CSV files from a folder using Power Query in SSIS. The M code works in Power BI but the SSIS package is erroring out because it can't identify the columns that the M script outputs.
