Reply
avatar user
Anonymous
Not applicable

How to use "IN" operator in power Query

Hi All,

 

Am new to Power BI and MSQL

I have a power query to import data from Microsoft Services where in i am currently using "OR" operator (Max 2) data values under a column to be checked while importing.

But my query should import data from service when this column is checked more than 2 values like FY 2015, FY 2016--- FY 2019.

 

Current PQuery:

 

EVALUATE
CALCULATETABLE (
ADDCOLUMNS (
"NPS Passive Count", 'NPS'[NPS Passive Count],
"NPS Advocate Count", 'NPS'[NPS Advocate Count],
"NPS Detractor Count", 'NPS'[NPS Detractor Count]
)
,OR ('Survey Response Date'[Financial Year] = "FY 2018" ,'Survey Response Date'[Financial Year] = "FY 2019")

)

 

Could you please help me with this at the earliest?

 

Thanks:

Nishanth

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

This is not Power Query, it's a DAX query. "Power Query" (also known by it's codename of "M") is the term given to the language used by the queries when you click on the "Get Data" button in Power BI.

 

To use the IN operator in DAX you would do the following:

 

EVALUATE
CALCULATETABLE (
ADDCOLUMNS (
"NPS Passive Count", 'NPS'[NPS Passive Count],
"NPS Advocate Count", 'NPS'[NPS Advocate Count],
"NPS Detractor Count", 'NPS'[NPS Detractor Count]
)
,'Survey Response Date'[Financial Year] IN { "FY 2018", "FY 2019"} )

)

View solution in original post

2 REPLIES 2
avatar user
Anonymous
Not applicable

Hi nmaheshwarappa,

 

You can multiple ORs in your logical condition.

 

Like,

 

OR('Survey Response Date'[Financial Year] = "FY 2016",(OR('Survey Response Date'[Financial Year] = "FY 2017",OR ('Survey Response Date'[Financial Year] = "FY 2018" ,'Survey Response Date'[Financial Year] = "FY 2019"))))

 

Regards,

Pradeep

d_gosbell
Super User
Super User

This is not Power Query, it's a DAX query. "Power Query" (also known by it's codename of "M") is the term given to the language used by the queries when you click on the "Get Data" button in Power BI.

 

To use the IN operator in DAX you would do the following:

 

EVALUATE
CALCULATETABLE (
ADDCOLUMNS (
"NPS Passive Count", 'NPS'[NPS Passive Count],
"NPS Advocate Count", 'NPS'[NPS Advocate Count],
"NPS Detractor Count", 'NPS'[NPS Detractor Count]
)
,'Survey Response Date'[Financial Year] IN { "FY 2018", "FY 2019"} )

)

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)