cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
username2345
Frequent Visitor

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

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 

1 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

 

View solution in original post

2 REPLIES 2
GilbertQ
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors