Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
martinfernandez
Frequent Visitor

Conditional Column with Power Query

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.

1 ACCEPTED SOLUTION

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

7 REPLIES 7
zoloturu
Memorable Member
Memorable Member

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!

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.

@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!

@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. 

@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!

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

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.