Forum Discussion
Can't refresh data after appending queries which are from different data source kinds
you can use UNION in DAX instead of appending/merging it in power query. it worked for me.
table_final = UNION(SELECTCOLUMNS(table_1,"col1",table_1[col1],"col2",table_1[col2]),SELECTCOLUMNS(table_2,"col1",table_2[col1],"col2",table_2[col2]))
Advocate IIWhen you say you can use UNION in DAX, do you mean on the front end visualization in PBI (not in Power Quary)? Sorry I've experimented with it in visualizations but am by no means an expert.
The reason I'm asking is I have a Common Data Service (v2) table (coming from xxxx.crm.dynamics.com and an onsite Analysis Services cube. I merge data from the CDS entity (table) to data imported from the cube, do a bunch of calcs and then use the resulting table(s) for even more calcs all in Power Query.
Everything refreshes fine on my desktop, but fails on PBI online once I do a merge/append/whatever in PowerQuery even though individual data sources are fine (gateway or direct credentials)
- sxt173
Hi. Just wanted to recheck if there is anything in place to fix this issue. It's a pretty big bug when you can't append/merge cloud data sources with on-premises even though both are being imported.
- Yes.DAX means in front end visualization of power bi desktop app and not in power query.
For append you can use union as mentioned above for merge you can use calculated column with lookup function - sxt173
gotcha, thanks!
So does anyone know if this is a bug that will be fixed or is there a technical reason cloud based DB's don't play nice with on-site sources via gateways? It's really inconvinient when mashing up data from 4-5 different sources and I need to do a lot of complex manipulation that is not easily done in DAX. I.e. I need to merge tables and then do multiple other calculations / create follow-on tables off of that merged table.
I could pull the on-site data into my CDS Environment which really really seems counterproductive, i.e. we're then duplicating DB tables in our data warehouse and also pulling same data into CDS just to get reporting working.