Traslate formula from Excel to DAX( PowerBI)

Question

hope everything is going well for everyone

Look, I have this table and the following excel formula to create a Key for each row on the table.

=CONCAT(IF(LENGTH(Loc.)=1;0;""); Loc.;EXTRACT(Nombre Institución Educativa;SEARCH(" "; Nombre Institución Educativa)+1;3);RIGHT(DANE 12;5);"-";IF(LENGTH(Sitio de Entrega)=1;0;""); Sitio de Entrega)

enter image description here

I need to traslate this formula to DAX (PowerBI), to create a new column called Key1, while import the data from a database. I'm a bit new on this though and I don't know how can I make it works the length, extract and search functions from Excel on DAX.

Thanks in regards if somebody know the answer and can help me

Answer 1

Key1 =
FORMAT ( 'MyTable'[Loc.], "0#" )
    & MID (
        'MyTable'[Nombre Institución Educativa],
        SEARCH ( " ", 'MyTable'[Nombre Institución Educativa] ) + 1,
        3
    )
    & RIGHT ( 'MyTable'[Dane 12], 5 ) & "-"
    & FORMAT ( 'MyTable'[Sitio de Entrega], "0#" )

Replace 'MyTable' with your actual table name. The formula does the following:

1. Pads Loc. with a 0
2. Gets the first 3 characters after the space in Nombre Institución Educativa
3. Gets the last 5 characters of Dane 12
4. Adds a - and pads Sitio de Entrega with a 0

The ampersand concatenates the string