Reply
Frequent Visitor
Posts: 7
Registered: ‎09-10-2018
Accepted Solution

Conditional Column with Power Query

[ Edited ]

Hi there,

 

I want to add a conditional column based on other table logic:

 

I have a "Due Dates Cube" table with this data:

 

Item IDSome other dataTransaction DateDue dateDays to expiration
1234Example data2018/09/102018/09/206
1235Other example data2018/09/012018/09/11-3

 

Where "Days to expiration" is calculated column that calculates:

each Duration.Days(DateTime.From([Due date]) - DateTime.LocalNow())

Then I have another table called "Time Periods" and it contains the following information:

 

Time Period CodeTime Period NameTime Period Days StartTime Period Days EndStart DateEnd Date
123Expires in 5 days052018/09/142018/09/19
124Expired between 5 days ago and today0-52018/09/142018/09/09

 

Edit: I forgot to mention that Start Date and End Date are calculated columns that add the respective days to the current date (2018/09/14).

 

I would like to add another column to "Due Dates Cube" table that calculates dynamically:

if [Due date] >= "Time Periods"[Start Date] and [Due date] <= "Time Periods"[End Date] then
    "Time Periods"[Time Period Code]

I am not very familiar with Power Query or Language M in order to write the previous conditional query. I think some kind of iteration process is needed to run this in the "Time Periods" table rows.

 

Any suggestions? Thanks in advance.


Accepted Solutions
Frequent Visitor
Posts: 7
Registered: ‎09-10-2018

Re: Conditional Column with Power Query

In fact, the code worked as expected. Thanks anyway.

 

My code:

 

Código

Time Period Code = 
CALCULATE(
    VALUES('Time Periods'[Time Period Code]),
    FILTER(
        'Time Periods',
        (ISBLANK('Time Periods'[Time Period Days Start]) || 'Time Periods'[Time Period Days Start] <= 'Due Date Cube'[Days to expiration])
        && (ISBLANK('Time Periods'[Time Period Days End]) ||Time Periods'[Time Period Days End] >= 'Due Dates Cube'[Days to expiration)
)
)

 

View solution in original post


All Replies
Advisor
Posts: 309
Registered: ‎01-16-2018

Re: Conditional Column with Power Query

Hi @martinfernandez,

 

Compare the same data types. You can't compare numeric with date. Make sure all your values used to compare are dates/numerics.

 

And it will be very helpful if you update a description of your question using screenshots with headers. Now they are separate. It is not easy to read and help you.

 

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

Frequent Visitor
Posts: 7
Registered: ‎09-10-2018

Re: Conditional Column with Power Query

Hi @zoloturu,

 

Thanks for answering, first of all.

 

I modified the question with the correct dates (to avoid comparing dfiferent data types).

 

I also "fixed" the table headers (since they were changed) to text.

Advisor
Posts: 309
Registered: ‎01-16-2018

Re: Conditional Column with Power Query

@martinfernandez,

 

To use columns from another table you need to use a command Advanced editor -> Home -> Merge queries. And use relationship column. Example - https://www.youtube.com/watch?v=xJ8cOKVyq4E

 

So use one table as main, then merge based on key column(s) and finally expand needed columns.

 

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

Frequent Visitor
Posts: 7
Registered: ‎09-10-2018

Re: Conditional Column with Power Query

[ Edited ]

@zoloturu,

 

Hi again,

 

What I saw regarding that way is I need (like you said) a key column. But instead I am working with dates. Besides, I would need only one column to merge but I also working with two columns (Start Date and End Date).

 

I am reading this post and perhaps this is the solution I need but using DAX. Therefore I rewrote the function so I could use it in my case by adding a DAX column in "Due Dates Cube" table:

Period = 
CALCULATE(
    VALUES('Time Periods'[Time Period Code]),
    FILTER(
        'Time Periods',
        'Time Periods'[Start Date] >= EARLIER( 'Due Dates Cube'[Due Date] )
        && 'Time Periods'[End Date] <= EARLIER( 'Due Dates Cube'[Due Date] )
    )
)

This fills the column value with an empty value. 

Advisor
Posts: 309
Registered: ‎01-16-2018

Re: Conditional Column with Power Query

@martinfernandez,

 

In both ways either M either DAX you would need to have key columns. To use DAX approach you need to set relationships between these two tables. Read for more details - https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships.

 

Both tables should have a column with exactly the same data type and some matching values. 

 

Then you can create a calculated column in a first table as per the example below:

 

 

IF( AND( Column1 >= RELATED(Column2) , Column1 <= RELATED(Column3) ) , RELATED(Column4), 0)

 

Documentation about RELATED function - https://msdn.microsoft.com/en-us/query-bi/dax/related-function-dax

 

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

Frequent Visitor
Posts: 7
Registered: ‎09-10-2018

Re: Conditional Column with Power Query

In fact, the code worked as expected. Thanks anyway.

 

My code:

 

Código

Time Period Code = 
CALCULATE(
    VALUES('Time Periods'[Time Period Code]),
    FILTER(
        'Time Periods',
        (ISBLANK('Time Periods'[Time Period Days Start]) || 'Time Periods'[Time Period Days Start] <= 'Due Date Cube'[Days to expiration])
        && (ISBLANK('Time Periods'[Time Period Days End]) ||Time Periods'[Time Period Days End] >= 'Due Dates Cube'[Days to expiration)
)
)

 

Highlighted
Community Support Team
Posts: 3,197
Registered: ‎02-06-2018

Re: Conditional Column with Power Query

Hi @martinfernandez,

 

It seems that you have solved your problem. Please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.