Function with dynamic database name not working online

[philippfrenzel]

Hi,

our company runs multiple affiliates on SAP B1, each affiliate is stored within it's own db-container like:

• SAP_US
• SAP_UK
• etc.

The schema within this db containers is always the same! So in PB-Desktop I could easily setup a settings table and added a custom column (invoke from function) to generate a table with a content column, containing the table from the different sap entities. 

Bringing this logic into the dataflow (online) the logic seems to work fine, but when I try to save the dataflow, it tells me that I'm trying to use dynamic datasources within one entity - and yes I want to do this on purpose. I don't wanna copy n paste the same script 16 times in order to retrieve data!

let
  Source = (varAffiliate as text) =>
    let
        localVarAffiliate = if Text.Length(varAffiliate) > 0 
            then varAffiliate
            else "SAP_Buchi_BIT" 
        ,Source = Sql.Database("BLTCHSAP003.buchi.com", localVarAffiliate)
...

And the invoked column looks like:

Table.AddColumn(#"Andere Spalten entfernt", "Content", each fx_fact_invoice([DBName]))

As said - this works fine in the preview - until I save...

If anyone has a solution for this - I would highly appreciate!

Thanks Philipp

[v-lid-msft]

Hi @philippfrenzel ,

Sorry for that, We can not found an effective solution. We suggest you to open a ticket here for help if you are a pro user: https://powerbi.microsoft.com/en-us/support/

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[philippfrenzel]

Dirty Cheat that solves the issue temporary:

let Source = (varAffiliate as text) =>
    let
        Source = if varAffiliate = "SAP_Buchi_BIT"
            then Sql.Database("BLTCHSAP003.buchi.com", "SAP_Buchi_BIT") 
            else if varAffiliate = "SAP_Buchi_BUS"
                then Sql.Database("BLTCHSAP003.buchi.com", "SAP_Buchi_BUS")
                else if varAffiliate = "SAP_Buchi_BUK"
                    then Sql.Database("BLTCHSAP003.buchi.com", "SAP_Buchi_BUK")
                    else if varAffiliate = "SAP_Buchi_BFR"
                        then Sql.Database("BLTCHSAP003.buchi.com", "SAP_Buchi_BFR")
                        else if varAffiliate = "SAP_Buchi_BHK"
                            then Sql.Database("BLTCHSAP003.buchi.com", "SAP_Buchi_BHK")
            else Sql.Database("BLTCHSAP003.buchi.com", "SAP_Buchi_xxx")
        ,EntityFactInvoiceSIN = Source{[Schema = "dbo", Item = "INV1"]}[Data]

It works but I have to maintain db-information in each file 😞 - well i have everything in GIT so I can search and replace BUT would love to pick it from my central sharepoint list!