Hello,
I have a large query which was creating multiple tables and then merging into one output table. This was working within Power BI Desktop but not on Power BI Service (for purposes of setting up scheduled refresh) with the Unable To Combine Data error that is common.
I followed the advice to break this query up into multiple staging queries which still did not work but allowed me to narrow down the issue to one part which is an SQL query. This is querying a very large table from an Azure SQL database and then merging it to a previously created table in the M query. This step looks like this:
#"SQL" = Sql.Database("REDACTED", "REDACTED", [Query="select a.business_partner_no, c.gender_desc as 'Gender',datediff(year, date_of_birth, getdate()) as 'Age', b.Max_club_member_creation_dt from cust.dim_business_partner a left join (select business_partner_no, max(club_member_creation_dt) as 'Max_club_member_creation_dt' from cust.dim_club group by business_partner_no) b on a.business_partner_no = b.business_partner_no left join cust.dim_gender c on a.gender = c.gender_id" & #"Generate list"]),
You can see here where the previous table is turned into a list to compare/merge with the sql query (as the Azure SQL db has millions of rows but the new table only has hundreds of rows.
I have not been able to find an answer on google about whether this is possible to do with power bi service. Other than bringing in all rows from the sql db in another query then merging (I tried this but it crashed PBI desktop due to the memory load) I am stuck as to how to get this to work.
As stated, this works totally fine in PBI desktop, just not on service.
Thanks!
