How to "convert" DAX code in M code: delete rows in calculated colum

0
votes

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

2
powerbidaxpowerquerym

2 Answers

0
votes

Here I have attached the .pbix file for your reference as all steps are difficult to mention and explain.

Report File Here

In short-

  1. I have duplicate your Table A first as Table A (2)
  2. Then performed some transformation to the new table Table A (2)
  3. Merge the new table Table A (2) to your Table A
  4. Then performed some steps in Table A

Please feel free to ask question if anything is not clear. Here below is the final output-

enter image description here

0
votes

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"