none
Transform complex SQL query to Power query for Power BI RRS feed

  • Question

  • Hi All,

    I am very raw for Power query and stuck in a problem where I have to transform very complex SQL query to Power query, the sql query is getting the data from multiple table using different joins followed by Groupby and having clause makes it more complex.

    I have created tables in PowerBI(Not in SQL) and now I want to execute the same SQL query using the advance editor in PowerBi, since I dont know much about the power query so I am not able to write syntax's for this.

    Below is the query, Please help me out in transforming the query to equivalent power query which I can run directly in editor to achieve the desired output by using PowerBI tables and query. 

    -------------------------------------------------------------------------------

    INSERT INTO ContactDemoCurrent_TIP ( ACCOUNT_NUMBER__C, [Account Name], [Sub Account Name], [Contact Name], TITLE, [Contacts Account Name], MAILINGSTREET, MAILINGCITY, MAILINGSTATE, MAILINGPOSTALCODE, MAILINGCOUNTRY, PHONE, FAX, EMAIL, TIP_WEB_ACCESS__C, TIP_STATEMENTS__C, TIP_STATEMENTS_DO__c, TIP_FINANCIALS__C, TIP_AUDIT_TAX_INFO_DO__c, TIP_CONFIRMS__C, TIP_CONFIRMS_DO__c, TIP_AUTHORIZED__C, MI_QR__C, REDEMPTIONS_BY_PHONE__C, [Unique ID] )

    SELECT SF_Investment.ACCOUNT_NUMBER__C, SF_Account.NAME AS [Account Name], SF_Subaccount.NAME AS [Sub Account Name], [SALUTATION] & " " & [FIRSTNAME] & " " & [LASTNAME] AS [Contact Name], ContactAccount.TITLE, ContactAccount.[Contacts Account Name], ContactAccount.MAILINGSTREET, ContactAccount.MAILINGCITY, ContactAccount.MAILINGSTATE, ContactAccount.MAILINGPOSTALCODE, ContactAccount.MAILINGCOUNTRY, ContactAccount.PHONE, ContactAccount.FAX, ContactAccount.EMAIL, SF_ReportingOptions.TIP_WEB_ACCESS__C, SF_ReportingOptions.TIP_STATEMENTS__C, IIf([TIP_STATEMENTS__C]="False","None",[SF_ReportingOptions]![TIP_STATEMENTS_DO__C]) AS Expr4, SF_ReportingOptions.TIP_FINANCIALS__C, IIf([TIP_FINANCIALS__c]="True",[TIP_AUDIT_TAX_INFO_DO__c],"None") AS Expr5, SF_ReportingOptions.TIP_CONFIRMS__C, IIf([TIP_Confirms__c]="True",[TIP_CONFIRMS_DO__c],"None") AS Expr6, SF_ReportingOptions.TIP_AUTHORIZED__C, SF_ReportingOptions.MI_QR__C, SF_Subaccount.REDEMPTIONS_BY_PHONE__C, [ContactAccount]![ID]+"_"+[SF_Investment]![ACCOUNT_NUMBER__C] AS Expr1

    FROM (((SF_Account INNER JOIN SF_Subaccount ON SF_Account.ID = SF_Subaccount.ACCOUNT__C) LEFT JOIN SF_Investment ON SF_Subaccount.ID = SF_Investment.SUB_ACCOUNT__C) INNER JOIN SF_ReportingOptions ON SF_Account.ID = SF_ReportingOptions.ACCOUNT__C) INNER JOIN ContactAccount ON SF_ReportingOptions.CONTACT__C = ContactAccount.ID

    GROUP BY SF_Investment.ACCOUNT_NUMBER__C, SF_Account.NAME, SF_Subaccount.NAME, [SALUTATION] & " " & [FIRSTNAME] & " " & [LASTNAME], ContactAccount.TITLE, ContactAccount.[Contacts Account Name], ContactAccount.MAILINGSTREET, ContactAccount.MAILINGCITY, ContactAccount.MAILINGSTATE, ContactAccount.MAILINGPOSTALCODE, ContactAccount.MAILINGCOUNTRY, ContactAccount.PHONE, ContactAccount.FAX, ContactAccount.EMAIL, SF_ReportingOptions.TIP_WEB_ACCESS__C, SF_ReportingOptions.TIP_STATEMENTS__C, IIf([TIP_STATEMENTS__C]="False","None",[SF_ReportingOptions]![TIP_STATEMENTS_DO__C]), SF_ReportingOptions.TIP_FINANCIALS__C, IIf([TIP_FINANCIALS__c]="True",[TIP_AUDIT_TAX_INFO_DO__c],"None"), SF_ReportingOptions.TIP_CONFIRMS__C, IIf([TIP_Confirms__c]="True",[TIP_CONFIRMS_DO__c],"None"), SF_ReportingOptions.TIP_AUTHORIZED__C, SF_ReportingOptions.MI_QR__C, SF_Subaccount.REDEMPTIONS_BY_PHONE__C, [ContactAccount]![ID]+"_"+[SF_Investment]![ACCOUNT_NUMBER__C], SF_Investment.LIQUIDATION_DATE__C, SF_Investment.FUND_TYPE__C, SF_Investment.ACTIVE__C

    HAVING (((SF_ReportingOptions.TIP_WEB_ACCESS__C)="TRUE") AND ((SF_Investment.LIQUIDATION_DATE__C) Is Null) AND ((SF_Investment.FUND_TYPE__C)="TIP") AND ((SF_Investment.ACTIVE__C)="True")) OR (((SF_ReportingOptions.TIP_STATEMENTS__C)="TRUE") AND ((SF_Investment.LIQUIDATION_DATE__C) Is Null) AND ((SF_Investment.FUND_TYPE__C)="TIP") AND ((SF_Investment.ACTIVE__C)="True")) OR (((SF_ReportingOptions.TIP_FINANCIALS__C)="TRUE") AND ((SF_Investment.LIQUIDATION_DATE__C) Is Null) AND ((SF_Investment.FUND_TYPE__C)="TIP") AND ((SF_Investment.ACTIVE__C)="True")) OR (((SF_ReportingOptions.TIP_CONFIRMS__C)="TRUE") AND ((SF_Investment.LIQUIDATION_DATE__C) Is Null) AND ((SF_Investment.FUND_TYPE__C)="TIP") AND ((SF_Investment.ACTIVE__C)="True")) OR (((SF_ReportingOptions.MI_QR__C)="TRUE") AND ((SF_Investment.LIQUIDATION_DATE__C) Is Null) AND ((SF_Investment.FUND_TYPE__C)="TIP") AND ((SF_Investment.ACTIVE__C)="True")) OR (((SF_ReportingOptions.TIP_AUTHORIZED__C)="TRUE") AND ((SF_Investment.LIQUIDATION_DATE__C) Is Null) AND ((SF_Investment.FUND_TYPE__C)="TIP") AND ((SF_Investment.ACTIVE__C)="True"))

    ORDER BY SF_Investment.ACCOUNT_NUMBER__C;

    ----------------------------------------

    Any help would be appreciated.

    Thanks,

    Aakash Rajput


    Aakash Rajput

    Tuesday, November 12, 2019 6:17 PM