Diff between dates with PowerQuery

[lguima]

Hello, I have the following data sample:

For each order, I need calculate how many days the customer took to make another order, so the dataset should stay:

I think the performance should be better if it's made on PowerQuery instead DAX, am I right?

How could I do that in Power Query? I think the selectRows function must be a solution, but I can't figure out how to make it work.

Any idea?

[lguima]

Hi @edhans thanks again!

That's why I thought to do that on Power Query. I'll need to use this Diff to make more calculations and change it in the modelling looked a good approach. I accepted as answer your previous comment and thank you so much, but if coud give me any advice on how to do that on Power Query level would be also great!

Thanks again!

[edhans]

Here is the problem with doing it in Power Query. You will need to do several Table.SelectRows() nested within other formulas, or merge the table with itself.

Those ALL incure performance issues on larger tables, even as few as 10,000 records. 

If I were forced to do this in Power Query, I would probably use List.Generate, but if it were my model, I'd still use a Calculated Column if you need it in a column. I'd prefer a measure. But there are times to use calculated columns, and Power BI can handle my code above on a 10,000,000 record table. Even List.Generate in Power Query would start to choke well before that. Power Query is fantastic for shaping tables, but it isn't good for analyzing tables, and here you are analyzing by scanning for the next order for the same customer.

[lguima]

Understood @edhans 

I really, really miss a tool to create data pipeline to populate the dataset, it would make everything easier in ETL.

Thanks!

[edhans]

I love Power Query, but I'd do this in DAX. Much more efficient at table scanning like this.

Days to Next Order =
VAR varCustomer =
    SELECTEDVALUE( 'Table'[Customer] )
VAR varOrder =
    SELECTEDVALUE( 'Table'[Order] )
VAR varCurrentDate =
    SELECTEDVALUE( 'Table'[Date] )
VAR varNextDate =
    CALCULATE(
        MIN( 'Table'[Date] ),
        'Table'[Customer] = varCustomer
            && 'Table'[Order] > varOrder,
        REMOVEFILTERS( 'Table' )
    )
VAR varDifference =
    INT( varNextDate - varCurrentDate )
VAR Result =
    IF(
        varDifference < 0,
        0,
        varDifference
    )
RETURN
    Result

It returns this @lguima 

[lguima]

Hi @edhans thanks for your answer!

I'm trying to follow your DAX example using a new column, I don't get any error but the result is always empty. I created a .pibx example, i think I'm missing something basic, could you help me?

Another point, I though that for performance it would be better try to do as much as we can in Power Query level, but now I think that's not the case, is that right?

Follow the attached file here

[edhans · Accepted solution]

That is a measure. I wouldn't use a column unless absolutely necessary.

That said, here is the same formula that will work in a column.

Column =
VAR varCustomer = 'Table'[Customer]
VAR varOrder = 'Table'[Order]
VAR varCurrentDate = 'Table'[Date]
VAR varNextDate =
    MINX(
        FILTER(
            'Table',
            'Table'[Customer] = varCustomer
                && 'Table'[Order] > varOrder
        ),
        'Table'[Date]
    )
VAR varDifference =
    INT( varNextDate - varCurrentDate )
VAR Result =
    IF(
        varDifference < 0,
        0,
        varDifference
    )
RETURN
    Result

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated Columns
Creating a Dynamic Date Table in Power Query