Union linked table with SQL

[Andrew_UK]

Hi All,

I have created a table in my model which is linked to the powerpivot. I have also created some SQL in the model which pulls back data. Both work correctly.

Ideally I need to be able to "union all" on these two data types. Union all on SQL is easy, I've seen tutorials on how to do it on 2 imported excel files. But I can't see how to Union differing source types and I have got error messages with every approach I've tried.

Any ideas?

For background it would be helpful to do this for the following reason; Our database contains all our invoice records but then our finance team also have an offline "Checked version" - it takes them on average 3 days to check these so I would like to take all Invoices less than 3 days old from our database and all invoices more than 3 days old from their file.

Andrew

 

[ImkeF]

You should do this in Power Query before loading to Powerpivot (if still necessary). In Power Query the UNION is called Append. You can select from different sources and filter to your needs before performing the append. https://support.office.com/en-us/ar...er-Query-e42ca582-4f62-4a43-b37f-99e2b2a4813a

It is not possible to perform a UNION in PP with different data sources.

 

[anvg]

Hi
If you have Excel 2016 then you can do union and join tables https://support.office.com/en-us/article/New-DAX-functions-8192a787-aa91-4d7f-9a82-4e2c607e629a
Regards,

 

[Andrew_UK]

Thanks guys,

I downloaded Power Query this afternoon and had a play with it with some online tutorials. It looks a lot of fun and lots of awesome tools in there, but I also feel like I'd need a 2 week training course in order to use it efficiently.

By contrast I've come up with a workaround which does what I wanted it to in PowerPivot. When we upgrade to 2016 (ETA later this year in this office) then I can ditch the workaround and neaten it up.

Best Regards,

Andrew

 

[barjoman]

I downloaded Power Query this afternoon and had a play with it with some online tutorials. It looks a lot of fun and lots of awesome tools in there, but I also feel like I'd need a 2 week training course in order to use it efficiently.

Andrew, I highly recommend this free course from Microsoft which provides a good foundation for using Power Query and PowerPivot. If you already know how to use PowerPivot, skip to the Power Query modules (Modules 3 and 4) which should only take you about 4 hours to complete.

https://www.edx.org/course/analyzing-visualizing-data-excel-microsoft-dat206x

 

[Andrew_UK]

Thanks for the link. I haven't had 4 free hours to sit down and do it yet.

However, I thought for others it may be useful to write an explanation of how I got this working. All Database tables contain a primary key, this is usually sequential in some way. So you can create a table in excel with the next few years worth of potential codes already populated - link this in to power pivot and then link both tables in to this.

It's kind of a union via another helper table.