Unable to combine data - one server with multiple databases using custom function

[username2345]

I am getting this error:

[Unable to combine data] Section1/Items Mst/Filtered To CurPrior Dates references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

when i try and refresh my report in powerbi service. The refresh works in power bi desktop. 

I have checked the privacy levels and they are all the same.

The query is the below:

let
ColNames = List.Transform(Table.ColumnNames(#"Filtered To CurPrior Dates"), each Text.Combine(Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"})(_), " ")),
Source = InvokeDB("item_mst", "site_ref,item, CreatedBy, CreateDate"),
#"Uppercased Text" = Table.TransformColumns(Source,{{"site_ref", Text.Upper, type text}}),
#"Filtered To CSENames" = Table.SelectRows(#"Uppercased Text", each List.Contains(#"CSE Users"[Name], [CreatedBy])),
#"Filtered To CurPrior Dates" = Table.SelectRows(#"Filtered To CSENames", each ( Date.IsInPreviousYear([CreateDate]) or Date.IsInCurrentYear([CreateDate]))),
#"Rename Columns" = Table.RenameColumns(#"Filtered To CurPrior Dates",List.Zip( {Table.ColumnNames(#"Filtered To CurPrior Dates"), List.Transform(ColNames, each Text.Proper(Text.Replace( _ , "_"," ")))})),
#"Changed Type" = Table.TransformColumnTypes(#"Rename Columns",{{"Create Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Site Ref", "Created By", "Create Date"}, {{"Lines Entered", each Table.RowCount(_), Int64.Type}}),
#"Add Type" = Table.AddColumn(#"Grouped Rows", "Type", each "Items", type text)
in
#"Add Type"

and I have a feeling it's because i am using InvokeDB function to combine multiple databases that live on the same server. 

This was the neatest way or I'd then have to have about 28 queries (and this is a fairly simple report) and the 4 databases are geographical and all follow the same structure

the custom function is as below:

(TblName, Qry) =>
let
Source1 = Sql.Database("server", "DB1", [Query="Select#(lf)"&Qry&"#(lf)FROM#(lf)"&TblName&"#(lf)"]),
Source2 = Sql.Database("server", "DB2", [Query="Select#(lf)"&Qry&"#(lf)FROM#(lf)"&TblName&"#(lf)"]),
Source3 = Sql.Database("server", "DB3", [Query="Select#(lf)"&Qry&"#(lf)FROM#(lf)"&TblName&"#(lf)"]),
Source4 = Sql.Database("server", "DB4", [Query="Select#(lf)"&Qry&"#(lf)FROM#(lf)"&TblName&"#(lf)"]),
Source = Table.Combine({Source1,Source2,Source3,Source4})

in
Source

Hoping there is a way to use a custom function like this to access the table in our database and will allow refresh 

[GilbertQ]

Hi @username2345 

I would suggest trying this from Ken Puls as this might solve your issue with the error you are facing

Power Query Errors: Please Rebuild This Data CombinationThe Excelguru Blog

[username2345 · Accepted solution]

I had seen this and just didn't want to add a bunch of stages as I have to access 5 dbs. I just removed the custom function and rewrote the queries to access the select statements and it's happy now

let
ColNames = List.Transform(Table.ColumnNames(#"Filtered To CurPrior Dates"), each Text.Combine(Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"})(_), " ")),
SourceUK = Sql.Database("Server", "DB4", [Query="Select#(lf)site_ref,item, CreatedBy, CreateDate#(lf)FROM#(lf)tbl#(lf)"]),
SourceUS = Sql.Database("Server", "DB3", [Query="Select#(lf)site_ref,item, CreatedBy, CreateDate#(lf)FROM#(lf)tbl#(lf)"]),
SourceAU = Sql.Database("Server", "DB2", [Query="Select#(lf)site_ref,item, CreatedBy, CreateDate#(lf)FROM#(lf)tbl#(lf)"]),
SourceDE = Sql.Database("Server", "DB1", [Query="Select#(lf)site_ref,item, CreatedBy, CreateDate#(lf)FROM#(lf)tbl#(lf)"]),
Source = Table.Combine({SourceUK,SourceUS,SourceAU,SourceDE}),
#"Uppercased Text" = Table.TransformColumns(Source,{{"site_ref", Text.Upper, type text}}),
#"Filtered To CSENames" = Table.SelectRows(#"Uppercased Text", each List.Contains(#"CSE Users"[Name], [CreatedBy])),
#"Filtered To CurPrior Dates" = Table.SelectRows(#"Filtered To CSENames", each ( Date.IsInPreviousYear([CreateDate]) or Date.IsInCurrentYear([CreateDate]))),
#"Rename Columns" = Table.RenameColumns(#"Filtered To CurPrior Dates",List.Zip( {Table.ColumnNames(#"Filtered To CurPrior Dates"), List.Transform(ColNames, each Text.Proper(Text.Replace( _ , "_"," ")))})),
#"Changed Type" = Table.TransformColumnTypes(#"Rename Columns",{{"Create Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Site Ref", "Created By", "Create Date"}, {{"Lines Entered", each Table.RowCount(_), Int64.Type}}),
#"Add Type" = Table.Buffer(Table.AddColumn(#"Grouped Rows", "Type", each "Items", type text))
in
#"Add Type