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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.