I thought to quickly connect a postgres database with Excel on Mac. Mac is a limitation from our company, so I can't use Windows.
I had thought there were quite some easy and advanced visual query building stuff going on on in Power Query/Excel, but it has proved to be the ride.
So finally:
1. Set up the driver
2. Set up the ODBC connection
3. Set up a query with parameters to get relevant data in excel
4. Feed this back into Power Query
I found the nice and handy MoreQuery component, this allows you to reference a table, so I can refresh ODBC and the second table is also refreshed nicely without the requirement to save the file and refresh the second table again.
In order to optimize further I realized MoreQuery also allows you to remove the table that ODBC connection created, and reference 'Existing connection'. However, this throws an error. Am I looking one step too far in optimizing this?
It just feels faster if Excel doesn't have to load 250.000+ rows for input into PowerQuery.
So, to resume, is it possible to allow to have PowerQuery linked to an ODBC source on Mac using the Morequery tool.
Reference: MoreQuery for Mac
Error 1004 during runtime: 'Method OLEDBConnection of object WorkbookConnection has failed (translated from Dutch)
I had thought there were quite some easy and advanced visual query building stuff going on on in Power Query/Excel, but it has proved to be the ride.
So finally:
1. Set up the driver
2. Set up the ODBC connection
3. Set up a query with parameters to get relevant data in excel
4. Feed this back into Power Query
I found the nice and handy MoreQuery component, this allows you to reference a table, so I can refresh ODBC and the second table is also refreshed nicely without the requirement to save the file and refresh the second table again.
In order to optimize further I realized MoreQuery also allows you to remove the table that ODBC connection created, and reference 'Existing connection'. However, this throws an error. Am I looking one step too far in optimizing this?
It just feels faster if Excel doesn't have to load 250.000+ rows for input into PowerQuery.So, to resume, is it possible to allow to have PowerQuery linked to an ODBC source on Mac using the Morequery tool.
Reference: MoreQuery for Mac
Error 1004 during runtime: 'Method OLEDBConnection of object WorkbookConnection has failed (translated from Dutch)
