Create Custom Column in Power Query (M language) from DAX Formula

[JMesquitaFilho]

I need to create a custom column, in power query (M language), using a formula developed in DAX language, as follows:

ValorAtual =

VAR ultimoipca =

    CALCULATE ( VALUES ( Ipca[Valor] ), TOPN ( 1, Ipca, Ipca[Data], DESC ) )

RETURN

    ROUND ( DIVIDE ( 'ReD'[Valor] * ultimoipca, [IpcaSoma] ), 2 )

'ReD'[Valor] - is a column of present table (ReD table)

Ipca[Valor] , Ipca[Data] and [IpcaSoma] - from other table (Ipca table)

Can someone help me please?

[serpiva64]

Hi,

it's much easier if you provide some sample data

[JMesquitaFilho]

Hi, serpiva64.

In the table below (ReD) I want to create the custom column:

The table below (Ipca) contains the information I will use to update data from the ReD table:

The formula for the update is as follows (in DAX language):

ValorAtual =

VAR ultimoipca =

    CALCULATE ( VALUES ( Ipca[Valor] ), TOPN ( 1, Ipca, Ipca[Data], DESC ) )

RETURN

    ROUND ( DIVIDE ( 'ReD'[Valor] * ultimoipca, [IpcaSoma] ), 2 )

Thanks for your attention.

[v-jingzhang · Accepted solution]

Hi @JMesquitaFilho 

What is the [IpcaSoma] in your DAX? Is it a measure or something else? I don't see this data in your screenshots of two tables. If possible, you can use some natural language and the sample data to explain what you want to calculate in the custom column. This could help us understand the expected result better. 

Assume you already have a [IpcaSoma] column in ReD table, you can add two steps to get the custom column. 

Step #1: Add a custom step to get the latest value from Ipca table

Step #2: Create the required custom step 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrS0tNA31Dc2VNJRcgFiXRNDUz1DYyDL0MDAQClWB1VJEBCbmxrApWMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t, CodNat = _t, Valor = _t, IpcaSoma = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type date}, {"CodNat", type text}, {"Valor", type number}, {"IpcaSoma", Int64.Type}}),
    
    // Add a custom step to get the latest value from Ipca table
    ultimoipca = Table.First(Table.Sort(Ipca, {"Data", Order.Descending}))[Valor],
    // Add a custom column 
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ValorAtual", each Number.Round([Valor] * ultimoipca / [IpcaSoma], 2), type number)
in
    #"Added Custom"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[JMesquitaFilho]

Thank you very much, @v-jingzhang.