Forum Discussion
Create Custom Column in Power Query (M language) from DAX Formula
I need to create a custom column, in power query (M language), using a formula developed in DAX language, as follows:
'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?
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.
4 Replies
Hi,
it's much easier if you provide some sample data
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 ) )RETURNROUND ( DIVIDE ( 'ReD'[Valor] * ultimoipca, [IpcaSoma] ), 2 )Thanks for your attention.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.
