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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
SaaM
Helper II
Helper II

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(
		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:

SaaM_0-1613658434125.png

Does somebody have the solution please ?

Thanks

 

 

7 REPLIES 7
v-eqin-msft
Community Support
Community Support

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

Hi @v-eqin-msft,

 

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"​

 

v-eqin-msft
Community Support
Community Support

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.

v-eqin-msft
Community Support
Community Support

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:

 

2.22.1.1.PNG

3.Extract values:

2.22.1.2.jpg

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

2.22.1.3.gif

 

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.

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

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

Hi @v-eqin-msft 

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

Thanks

kind regards

Saam

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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