Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
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"
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.
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://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
User | Count |
---|---|
141 | |
70 | |
70 | |
53 | |
53 |
User | Count |
---|---|
208 | |
95 | |
64 | |
60 | |
57 |