Live connection can't use append queries

[katan]

I have two queries need all data(something like full join or append queries function) at live connection

queries1:

select SalesID,InvoiceID,InvoiceDate,InvoiceAccount,CurrencyCode,NumberSequenceGroup,SalesType,month(InvoiceDate) as month,year(InvoiceDate) as year,
Case NumberSequenceGroup When 'CS' Then 'CASH' Else 'CREDIT' End As Term from CustInvoiceJour
where DataAreaID='XXX' and SalesType=3 and NumberSequenceGroup in ('CS') and SalesID<>''

queries2:

select a.SalesID,a.InvoiceID,a.InvoiceDate,a.InvoiceAccount,a.CurrencyCode,a.NumberSequenceGroup,a.SalesType,month(a.InvoiceDate) as month,year(a.InvoiceDate) as year,a.InvoiceAmountMST as CnDn,b.MBP_CustInvoiceType
from CustInvoiceJour a Join CustInvoiceTable b On a.InvoiceID=b.InvoiceID And a.InvoiceDate=b.InvoiceDate and a.NumberSequenceGroup=b.NumberSequenceGroup And b.Posted=1 and
(b.MBP_CustInvoiceType=1 or b.MBP_CustInvoiceType=2) And b.DataAreaID='XXX'
where a.DataAreaID='XXX' and a.SalesType=0 and a.NumberSequenceGroup in ('CS')

how can I got all data?Thanks a lot.

[v-piga-msft · Accepted solution]

Hi @katan,

As I know, If you connect data with live connection, Power Query is not avaliable in Power BI Desktop. In fact, the Edit Queries and all related options of that are disabled in the Live Connection mode.

So all data transformation needs must be handled before loading data into SSAS model. Because SSAS is not a data transformation tool, you can leverage SSIS to do the data transformation before loading data into the data warehouse, and then process data from the data warehouse into an SSAS data model. 

For more details, you could have a reference of this blog.

Best Regards,

Cherry

[katan]

Thank you for your help. I need take time to study your reference blog.