Forum Discussion

username2345's avatar
username2345
Frequent Visitor
4 years ago
Solved

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...
  • username2345's avatar
    username2345
    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