CODEWITHSUNDEEP
excelpowerquerysequel
Gary
Gary

Reputation: 51

Excel Power Query to change Sequel Database Source dynamically from a Drop-Down List

I have the below Code in a Power Query which works well for me

let
    MonthNo= Text.From(Excel.CurrentWorkbook(){[Name="MonthNo"]}[Content]{0}[Column1]),                                 
    Source = Sql.Database("VMXX\SAGE200", "UK_XXXXXA_2024",.......
    .............
    .......(lf)AND SYSAccountingPeriod.PeriodNumber ="&MonthNo&"

Where "UK_XXXXXA_2024" is one of many Company Databases we have. Similar to my 'MonthNo' above which is driven from a cell in my excel sheet as a 'MonthNo' Named Range, I want to Code into the same Code a Named Range called 'DBase' which will be a Validation Drop-Down List in a cell in my Excel Sheet of all our Company Databases we have, so that on changing the Company Database in the Validation Drop-Down List, the 'Source' of my Data Query will change dynamically for Example, the above "UK_XXXXXA_2024" will change to say "UK_XXXXXB_2024" or other within my List.

I have tried inserting as with the 'MonthNo' above as follows with a Named Range Cell being Dbase

let
    DBase= Text.From(Excel.CurrentWorkbook(){[Name="DBase"]}[Content]{0}[Column1]),
    MonthNo= Text.From(Excel.CurrentWorkbook(){[Name="MonthNo"]}[Content]{0}[Column1]),                                 
    Source = Sql.Database("VMXX\SAGE200", "&Dbase&",.......

but am not having much joy currently, hopefully a simple fix and not something silly I am doing.

Many thanks

Gary

Upvotes: 0

Views: 61

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12161

You don't need the quotes and ampersands:

 Source = Sql.Database("VMXX\SAGE200", Dbase, .......

Upvotes: 1

Related Questions