Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Function with dynamic database name not working online

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

Dataflow

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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!

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    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.