Convert a formula from dax to m lenguaje

[CarmeloSanchez]

Hi everyone,

First of all thanks for reading me,

I need to convert to m a formula that I'm using on dax.

The aim of the formula is to check if the same department (departamento) has more than one register for the same contract number (Nº Contrato) and if there is more than 1 dont take the line that is "Generic" is formula is the following and works correctly on dax, but i need to use it in power query instead of dax. The formula is the following:

Filter = 
VAR _Contrato = [Nº Contrato]
VAR _Departamento = [Departamento]
RETURN
IF(
    AND(
        [Recurso] = "Generic",
        COUNTX(FILTER(ALL('GANTT'),[Nº Contrato]=_Contrato &&[Departamento]=_Departamento),[Recurso])>1
    ),
    "False",
    "True"
)

 Thanks in advace, I will mark as solve the best answer.

[rohit_singh · Accepted solution]

Hi @CarmeloSanchez ,

Assuming your data looks like this :

Please try the following in Power Query 

1) Apply group by transformation on columns departamento and Nº Contrato to count the number of registers.

2)  You will now see the count of registers in a new column. Filter the column "Count" to be <= 1

This will omit any rows with register count > 1 and will give you the final result.

Here is the M-code for your reference :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MgaSBYVFSrE6yCIRkVFwERNTMyAZ5uQHF7E0A6lxDQpRio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [departamento = _t, #"Nº Contrato" = _t, Recurso = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"departamento", type text}, {"Nº Contrato", Int64.Type}, {"Recurso", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"departamento", "Nº Contrato"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Count] <= 1)
in
    #"Filtered Rows"

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[CarmeloSanchez]

Thank you so much, it helped a lot

[rohit_singh]

@CarmeloSanchez , Happy to help! Please do press the thumbs up button to leave me kudos ! Really appreciate it 🙂