cancel
Showing results 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

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:

Does somebody have the solution please ?

Thanks

7 REPLIES 7
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

Helper II

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,
in

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,
in

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

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.

Community Support

Hi @SaaM ,

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

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.

https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/power-bi-data-sources/p...

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.

Helper II

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

Community Support

Hi @SaaM ,

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

Best Regards,
Eyelyn Qin

Helper II

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

Thanks

kind regards

Saam

Announcements

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 Monthly Update - June 2024

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

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors