Direct Query with Parameter Partition Error

Anonymous · ‎05-13-2022

Running into an issue setting up parameterized direct query in power query. I currently have the power query advanced editor executing against the query below and it actually works in power query.

let
    Results = 
    Text.Combine(
        {
            "
            select *
            from (
                select a.ID, a.NewValue, a.OldValue
                from sf.LH a
                join sf.L b on a.LeadId = b.Id
                where b.CAI = '", #"PARAM_Selected Account", "'
            
                union all
            
                select a.ID, a.NewValue, a.OldValue
                from sf.CH a
                join sf.C b on a.ContactId = b.Id
                where b.AI = '", #"PARAM_Selected Account", "'
            ) X
            "
        }
    ),
    Source = Sql.Database("X", "Y", [Query=Results])
in
    Source

I get an error however when I close and apply to make the changes back to the model below:

Failed to save modifications to the server. Error returned: 'DirectQuery partition 'LU_History-e698b91f-643c-4242-bb5c-8151a9911992' has '2' datasource reference(s) in its expression which is not allowed. '

Any thoughts into what causes this to fail when trying to commit back to the model and how to get the parameterized DQ configuration to function?