Power Query Source - SSIS with function not working

[DieZarAx64]

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://community.powerbi.com/t5/Power-Query/Load-the-data-by-using-power-query-to-SQL-database/m-p...

@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!

[keobrie]

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.

[v-jingzhang]

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.

[DieZarAx64]

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?