Forum Discussion
username2345
4 years agoFrequent 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...
- 4 years ago
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
GilbertQ
Super User
4 years agoHi 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
- username23454 years agoFrequent Visitor
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