Hi Guys,
this is some kind of continuity from here:
https://www.excelforum.com/excel-pro...ml#post4917914
How to write sql statements in M?
I want to get :
so simple sql in power query M language.
Please Login or Register to view this content.
In attachment i addded Vba to create query and and this query to workbook for PQ.
Now time for SQL code for this...
Please help,
Best,
Jacek
You don't write SQL in M. You load the two tables into PQ, then merge them in a query.
Don
Please remember to mark your thread 'Solved' when appropriate.
yes xlitnitwit.
But PQ is using M language where you can do all things line in normal SQL.
So within Let and In you can write a code for this ;-)
Best,
Jacek
Half the point of using PQ is that it has a user interface to do that for you!
I was also just coming back to amend my earlier post, since if you are using ODBC you can pass SQL queries using the Odbc.Query() function to extract just the source data you want.
agreeHalf the point of using PQ is that it has a user interface to do that for you!
Odbc.query will create listobjects from odbc source yes?I was also just coming back to amend my earlier post, since if you are using ODBC you can pass SQL queries using the Odbc.Query() function to extract just the source data you want.
So will create query and recordset will be odbc recordset?
Best,
Jacek
Last edited by jaryszek; 06-14-2018 at 06:18 AM.
Assuming you choose to load the data to a table, yes.Originally Posted by jaryszek
I am thinking how it can help me with power query.
I will create ODBC connection and set recordset.
And next i will set ODBC.query() to power query.
So SQL only i should perform using ODBC connections.
It is a pity that Power Query has no possibilty to write normal sqls - it would be awesome!
Best,
Jacek
Why? What are you trying to do that you can't do with PQ?
I am trying to write left join there.SELECT t_current.*
FROM t_current LEFT JOIN t_previous ON t_current.Order= t_previous.Order
WHERE t_previous.Order Is Null
And create variables for fields and records.
Best,
Jacek
You can do that when merging two tables.
I don't know what you mean by that as regards PQ. Can you be more specific?And create variables for fields and records.
Thank you,
where can i add where clause to it?
I mean that i have to have universal code for each comparing tables.And create variables for fields and records.
One time i will have 3 columns within table and sometimes 10.
So macro will have to loop through each table, add to PQ, merge them using joins.
So knowing M language here to create your commands is very important, manually i can do only sql join here for one table.
Best,
Jacek
You use filters.
Good luck. I only have 2010 here, which does not expose PQ.I mean that i have to have universal code for each comparing tables.
One time i will have 3 columns within table and sometimes 10.
So macro will have to loop through each table, add to PQ, merge them using joins.
So knowing M language here to create your commands is very important, manually i can do only sql join here for one table.
Thank you,
anyone know M language?
Best,
Jacek
Why do you need it (there are reference documents available online)? If you do it manually in the UI, PQ will write the M code for you. Assuming you have programmatic access to the M code, you can then amend that as needed.
Hi xlnitwit,
Yes, i can record macro and show what code is for this.
But first of all i have to create good queries ;-) And learn how to do it within power query.
Best,
Jacek
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks