Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all -
I'm looking to create a conditional column in Power Query which would analyze 3 columns to output a 'looked up value' (date). This was probably not explained very well. But this is what I want:
I'm looking to return the NEXT nearest date for all rows with column 'type' = I. If it says, I, that means we want the next nearest date for where 'type' is O (matching with 'number'. For all rows with 'O' we can leave it as blank.
Is there a way to do this via condtional column? Or would I have to do a custom column in Power Query or DAX? Thank you in advance.
Solved! Go to Solution.
Hi , @whatisdata96
Here are the steps you can refer to :
(1)My test data is the same as yours.
(2)We can create a calculated column in your table:
Column = var _cuurent_date = 'Table'[Date]
var _tO =SELECTCOLUMNS( FILTER( 'Table' , 'Table'[Type]= "O") , "Date" , [Date])
var _compare =ADDCOLUMNS( CROSSJOIN( {_cuurent_date} , _tO) , "days" ,ABS( DATEDIFF([Date] ,[Value] ,DAY)))
var _min_days = MINX(_compare, [days])
var _mindate_table = DISTINCT( FILTER( _compare , [days] = _min_days))
return
IF([Type]="I", CONCATENATEX(_mindate_table,[Date]), BLANK())
(3)Then we can meet your need , the result is as follows:
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi , @whatisdata96
Here are the steps you can refer to :
(1)My test data is the same as yours.
(2)We can create a calculated column in your table:
Column = var _cuurent_date = 'Table'[Date]
var _tO =SELECTCOLUMNS( FILTER( 'Table' , 'Table'[Type]= "O") , "Date" , [Date])
var _compare =ADDCOLUMNS( CROSSJOIN( {_cuurent_date} , _tO) , "days" ,ABS( DATEDIFF([Date] ,[Value] ,DAY)))
var _min_days = MINX(_compare, [days])
var _mindate_table = DISTINCT( FILTER( _compare , [days] = _min_days))
return
IF([Type]="I", CONCATENATEX(_mindate_table,[Date]), BLANK())
(3)Then we can meet your need , the result is as follows:
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
HI
try this measure :
var daterech=selectedvalue(Table[Date])
return
calculate(Min(Table[Date]), filter(all(Table), Table[Date]>daterech))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |