Forum Discussion

SaaM's avatar
SaaM
Helper II
5 years ago

Equivalent DAX Formula in Power query ?

Hi everybody, It s my first post here.   I am trying to convert a DAX formula in Power Query and so far I have not find the solution   My DAX formula is :         Column = IF( ISBLANK( ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi SaaM ,

 

Based on my test,In Power Query,you have to consider many combinations of possibilities to create a correct result:

 

1.Add a Index column(From 0) 

2.Create a custom column using the following formula:

 

3.Extract values:

4. Change data type and use Fill down to replace the null value:

 

So I prefer to do it using DAX. And you could use variable to simplify the code like this:

Column = 
VAR _v =
    MINX (
        FILTER (
            'Table',
            AND (
                'Table'[item] = EARLIER ( 'Table'[item] ),
                'Table'[line_number] > EARLIER ( 'Table'[line_number] )
            )
        ),
        'Table'[line_number]
    )
RETURN
    IF ( ISBLANK ( _v ), 'Table'[line_number], _v )

Here is the pbix file.

 

Please refer to :

https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/power-bi-data-sources/power-query-get-value-from-previous-row/

https://radacad.com/m-or-dax-that-is-the-question

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

SaaM's avatar
SaaM
Helper II
5 years ago

Thanks for the reply but the solution is not working for me as I have this error at step 3:

The field is a list but i cannot extract it.

Expression.Error: A cyclic reference was detected during the evaluation.

 

And it works with one item only, if you have multiple items it does not work so well, I did not mention that in my question, my bad ....

 

Thanks anyway

Regards

Saam

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi SaaM ,

     

    So could you please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business after removing sensitive data?

     

    Or as I suggested before, It will be easier to use DAX.

     

    Best Regards,
    Eyelyn Qin

    • SaaM's avatar
      SaaM
      Helper II
      5 years ago

      Hi Anonymous 

      If I go for the DAX solution can I use it in power query formula ?

      Thanks

      kind regards

      Saam