cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rena
Helper III
Helper III

Power Query equivalent of SQL request

Hello Everyone,

 

Can somebody please help me traduce this sql request on Power Query ?

 

sql : select * from [table] where date_periode = format(getutcdate(),MM) and colonne_name not in
(select colonne_name from [table] where date_periode = format(dateadd(m,getutcdate(),-1),MM)).

 

Thank you. 

2 ACCEPTED SOLUTIONS
BA_Pete
Super User
Super User

Hi @Rena ,

 

You can just write your SQL query directly into Power Query.

When you select SQL Server as a new source, you can type your SQL here:

BA_Pete_0-1645522221630.png

 

Make sure to update the Value.NativeQuery step with the EnableFolding parameter to preserve query folding functionality, like this:

BA_Pete_1-1645522343070.png

 

Pete



Now accepting Kudos!

Proud to be a Datanaut!




View solution in original post

The answer that worked for me :

Flag = 
var _preMonth=DATEADD('Table'[Month],-1,MONTH)
var _preEmps= SUMMARIZE(FILTER(ALL('Table'),YEAR([Month])= YEAR(_preMonth) && MONTH([Month])=MONTH(_preMonth)) ,[Name of Employees])
return IF(_preMonth<>BLANK(),IF( MAX('Table'[Name of Employees]) in _preEmps,0,1))

View solution in original post

7 REPLIES 7
edhans
Super User
Super User

Hi @Rena - instead of asking for people to translate SQL into M, ask the question you are trying to answer.

It seems you want the data filtered in a specific way. You can filter based on the current date. Just filter by any date to get the Table.SelectRows function written, then change the date to 

Date.ToText(DateTime.Date(DateTimeZone.UtcNow()), "MM")

 

That returns 02 for today.

But give us some data to work with.

as @BA_Pete said there is a Value.NativeQuery parameter you can use, but you should be well versed in how it works for additional troubleshooting steps, and if you are just getting started with M, you could be in for issues later if things break and you don't fully understand what is going on. Power Query PostgreSQL connector - Power Query | Microsoft Docs has more info. this is on PostgreSQL, but same holds for SQL Server.

 

As I suggest, give us some source data to work with and an explanation of what you are trying to do. Converting code from SQL to M is often possible, and is often not the right way to go about it. Power Query may have a more efficient way for Power Query to do it vs replicating SQL logic. Same goes for DAX functions, Excel formulas, etc.

 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

I had an excel file with the list of employees of a company by month and that list was updated every month. (See example below). I would have liked to create a visual on power bi that allows me to display only the new employees of the current month compared to the previous month.

 

For example in January we had John, Carter, Kim and Alexander.

In February we have John, Carter, Kim, Alexander and Sienna.

The visual should have then show me only Sienna.

 

Hi, @Rena 

 

Is the above post helpful to you?If  it does, could you please mark the post which help as Answered?

This way people are more willing to help you solve your problems.

And it will help the others in the community find the solution easily if they face the same problem with you. Thank you.

 


@Rena wrote:

I had an excel file with the list of employees of a company by month and that list was updated every month. (See example below). I would have liked to create a visual on power bi that allows me to display only the new employees of the current month compared to the previous month.

 

For example in January we had John, Carter, Kim and Alexander.

In February we have John, Carter, Kim, Alexander and Sienna.

The visual should have then show me only Sienna.

 


This seems to be a new question, my suggestion would be to create a new thread if you have a new question.

Since you've already posted your question, to save you time I'll post my thoughts on the problem here. But it is still recommended to create a new post.

 

This seems to be an HR analysis issue. You can refer to the following article.

HR Analytics - Active Employee, Hire and Termination trend

Customer Retention Part 1: Month on Month Retention

Customer Retention Part 2: Period over Period Retention

In this article, we will cover the following HR Analytics calculations.

  1. Active Employees: Current Employees
  2. Hired Employees
  3. Terminated/ Separated Employees
  4. Last Period Active Employees: Last Period Employees
  5. Period over Period Change %: Employee Change% 

vangzhengmsft_0-1645688228088.png

 

More related:

https://community.powerbi.com/t5/Desktop/Help-with-Report/td-p/2183276

https://community.powerbi.com/t5/Desktop/Counting-Reoccurrences-New-and-Completed-Entries-based-on-m...

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



The answer that worked for me :

Flag = 
var _preMonth=DATEADD('Table'[Month],-1,MONTH)
var _preEmps= SUMMARIZE(FILTER(ALL('Table'),YEAR([Month])= YEAR(_preMonth) && MONTH([Month])=MONTH(_preMonth)) ,[Name of Employees])
return IF(_preMonth<>BLANK(),IF( MAX('Table'[Name of Employees]) in _preEmps,0,1))

Hello edhans,

 

Thank you  for your response and advices. I will apply them next Time for sure.

 

i have already found the answer to My problem.

 

Best regards,

 

 

BA_Pete
Super User
Super User

Hi @Rena ,

 

You can just write your SQL query directly into Power Query.

When you select SQL Server as a new source, you can type your SQL here:

BA_Pete_0-1645522221630.png

 

Make sure to update the Value.NativeQuery step with the EnableFolding parameter to preserve query folding functionality, like this:

BA_Pete_1-1645522343070.png

 

Pete



Now accepting Kudos!

Proud to be a Datanaut!




Greg_Deckler
Super User
Super User

@edhans @ImkeF 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors