I am working with Power Bi Desktop and i am trying to "convert" a DAX code into M code. I know that this is not really possible, because of M´s structure and intention. But i have seen other Programmers achieve their DAX code with some workarounds in M code.
To my Problem: I need to find a way, to delete rows in Power Query editor, after i calculated, if that row is already in the table. Specifically: If the column [FIN] is identical AND the column [Laufleistung in km] is not higher than +=30, i count that row as a duplicate and therefore want to delete tis row. I need to achieve the following DAX Column, within Query Editor:
Count Doppelte = CALCULATE(
COUNT('Table A'[FIN]);
FILTER('Table A'; 'Table A'[FIN] = EARLIER('Table A'[FIN])) ;
FILTER('Table A'; 'Table A'[Laufleistung in km] <= EARLIER('Table A'[Laufleistung in km])+30) ;
FILTER('Table A'; 'Table A'[Laufleistung in km] >= EARLIER('Table A'[Laufleistung in km])
)
)
With this Column i count (so result in column is 2), whenever i have a duplicate row. The result looks like this:
| FIN | Laufleistung in km | Doppelte |
|---|---|---|
| ID001 | 500 | 1 |
| ID004 | 200 | 1 |
| ID001 | 529 | 2 |
| ID004 | 205 | 2 |
| ID001 | 700 | 1 |
So, i want to delete the rows with [Doppelte]=2.
I currently do not know how to achieve this calculation in Power Query Editor. DO you guys have any knowledge on how to do this?
Thank you, Marcel :)
Edited answer. See if this works for you
For each [FIN] it finds the largest [Laufleistung in km] from all prior rows, and compares the value to the current [Laufleistung in km] to see if they are at least 30 different
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index","MaxD",(i)=>List.Max(Table.SelectRows(#"Added Index", each [FIN]=i[FIN] and [Index]<i[Index]) [Laufleistung in km]), type number ),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Doppelte", each try if [Laufleistung in km]-[MaxD] < 30 then 2 else 1 otherwise 1),
in #"Added Custom1"