none
Can I make a call to a variable in a BI Query to a other BI Query RRS feed

  • Question

  • For each different customer I want to make a special BI Query.  Because each customer may not see information of the other customers, I mus t filter on the customer in the SQL Statment.

    I have 2 BI Power Query's (BiCustomer, BiUsers)

    1. BiCustomer.

    let
        strCompanyName= "DemoCust4",
        Bron = Sql.Database("br7xlhkcoz.database.windows.net", "ManagementDatabaseTrunk", 
        [Query="SELECT Id,Organization,CompanyName  FROM CcnCustomers where CompanyName='" & strCompanyName & "';"])

    in
        Bron

    2. BiUsers

        

    let 

      Bron = Sql.Database("br7xlhkcoz.database.windows.net", "ManagementDatabaseTrunk",
     [Query="SELECT CcU.Id,CcU.Name,CcU.CcnCustomerId,CcU.Firstname,CcU.Lastname FROM CcnUsers as CcU inner join (Select Id,CompanyName From CcnCustomers Where CompanyName='DemoCust4') As CCN on CCN.Id=CcU.CcnCustomerId   ;"])

    in
        Bron

    Is it possible that in the second Query (Biuser) I make a call to the first Query and grep the variable  strCompanyName

    Or is it posslible to connect to the tempory result in Power BI of the first query.. somthing like BiCustomer.CompanyName

    Or is there a different solution


    Wednesday, January 27, 2016 10:26 AM

Answers

  • If I understood you right, easiest would be to create a separate query named "strCompanyName" like this:

    let
        Source = "DemoCust4"
    in
        Source

    Just skip the first line of your 1st query (otherwise you'd have defined it twice) - even no need to further adjust any of the existing code.


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Eric Krom Wednesday, January 27, 2016 2:19 PM
    Wednesday, January 27, 2016 1:37 PM
    Moderator

All replies

  • If I understood you right, easiest would be to create a separate query named "strCompanyName" like this:

    let
        Source = "DemoCust4"
    in
        Source

    Just skip the first line of your 1st query (otherwise you'd have defined it twice) - even no need to further adjust any of the existing code.


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Eric Krom Wednesday, January 27, 2016 2:19 PM
    Wednesday, January 27, 2016 1:37 PM
    Moderator
  • Tanks.... 
    Wednesday, January 27, 2016 2:18 PM