Equivalent DAX Formula in Power query ?

[SaaM]

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(
		MINX(
			Filter(
				Table,  
				and(
					Table[item] = EARLIER(Table[item]), 
					Table[line_number] > EARLIER(Table[line_number])
					)
				), 
			Table[line_number]
			)
		),
	Table[line_number],
	MINX(
			Filter(
				Table,  
				and(
					Table[item] = EARLIER(Table[item]), 
					Table[line_number] > EARLIER(Table[line_number])
					)
				), 
			Table[line_number]
			)
)

 My result for one item is like this:

Does somebody have the solution please ?

Thanks

[Anonymous]

Hi @SaaM ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

Best Regards,
Eyelyn Qin

[SaaM]

Hi @Anonymous,

The solution you proposed is not fitting 100% to my problem, that's the reason why I didn t accept it as solution.

The solution in M is as follow:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMDA1UNJRMlSK1UHiGqFyzVG5FqhcQwME3xDIN0XloksbGqLx0dQboRsH5McCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Column = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}, {"Column", Int64.Type}}),
    fnPrevMaxInLine = (tbl as table, item as number, val as number) => let 
        FilterTbl = Table.SelectRows(tbl, each ([Item] = item and [Column] < val)),
        MaxCol = List.Max(FilterTbl[Column])
        in MaxCol,
    #"Added Custom" = Table.AddColumn(#"Changed Type", "PrevMax", each fnPrevMaxInLine(#"Changed Type", [Item], [Column]))
in
    #"Added Custom"

Kind regards

Saam

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMDA1UNJRMlSK1UHiGqFyzVG5FqhcQwME3xDIN0XloksbGqLx0dQboRsH5McCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Column = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}, {"Column", Int64.Type}}),
    fnPrevMaxInLine = (tbl as table, item as number, val as number) => let 
        FilterTbl = Table.SelectRows(tbl, each ([Item] = item and [Column] < val)),
        MaxCol = List.Max(FilterTbl[Column])
        in MaxCol,
    #"Added Custom" = Table.AddColumn(#"Changed Type", "PrevMax", each fnPrevMaxInLine(#"Changed Type", [Item], [Column]))
in
    #"Added Custom"​

[Anonymous]

Hi @SaaM ,

No.

M Language and DAX are the two languages supported by Power BI to manipulate, filter and analyse the data. Though both languages have similar functionalities, they operate independently of each other. M is used to pre-process the data inside the Query Editor whereas DAX is used to analyse the data after the data is loaded into the Data View Model.

In essence: First you use Power Query (M) to query data sources, clean and load data. Then you use DAX to analyze the data in Power Pivot. Finally, you build pivot tables (Excel) or data visualisations with Power BI.

Some discussion on when to choose which(DAX or M) is here and here

Refer to : Differences between the M Language and DAX in Power BI

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.

[Anonymous]

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/p...

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]

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]

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]

Hi @Anonymous 

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

Thanks

kind regards

Saam