Power Query equivalent of SQL request

[Rena]

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. 

[BA_Pete · Accepted solution]

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:

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

Pete

[edhans]

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.

[Rena]

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.

[v-angzheng-msft]

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% 

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.

[Rena · Accepted solution]

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))

[Rena]

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 · Accepted solution]

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:

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

Pete

[Greg_Deckler]

@edhans @ImkeF