Forum Discussion

craig811's avatar
craig811
Helper III
6 years ago
Solved

Help with custom column

Hi,

 

How can I create a custom column to take at value from my other table in power query:

 

I am using the below but does not work:

Exchange_Rates[Exchange Rate],FILTER(Exchange_Rates, Combined_for_GL_Check[Entered Currency]= Exchange_Rates[Currency]

Data Protection
General Comment
Need Help
Show and Tell
Tips and Tricks
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi craig811 ,

     

    We can create a custom column and expand it to meet your requirement:

     

    let c = [Entered Currency]
in Table.SelectRows(Exchange_Rates,each [Currency] = 
c)

     

     

    All the queries are here:

    Exchange_Rates:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUbJUitWJVnICsgwNwExnENMQzHQBMs3BLFcgy1gpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Currency = _t, #"Exchange Rate" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Currency", type text}, {"Exchange Rate", Int64.Type}})
in
    #"Changed Type"

     

    Combined_for_GL_Check:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSBUy6KsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Entered Currency" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entered Currency", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Rate", each let c = [Entered Currency]
in Table.SelectRows(Exchange_Rates,each [Currency] = 
c)),
    #"Expanded Rate" = Table.ExpandTableColumn(#"Added Custom", "Rate", {"Exchange Rate"}, {"Rate.Exchange Rate"})
in
    #"Expanded Rate"

     

     

    Or we can create a calculated column to meet your requirement:

     

    Rate_Dax = LOOKUPVALUE('Exchange_Rates'[Exchange Rate],'Exchange_Rates'[Currency],[Entered Currency])

     

     
    Best regards,

     

4 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    hi craig811 

    you are trying to do it in Power Query Editor, but as you have so simple statement it should be enough to create relationships between Combined_for_GL_Check[Entered Currency] and Exchange_Rates[Currency] fields (if it has 1-to-1 or manyt-to-one connections).

    you will bw able to add this value in other places of your report without additionalcoding

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi craig811 ,

     

    We can create a custom column and expand it to meet your requirement:

     

    let c = [Entered Currency]
in Table.SelectRows(Exchange_Rates,each [Currency] = 
c)

     

     

    All the queries are here:

    Exchange_Rates:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUbJUitWJVnICsgwNwExnENMQzHQBMs3BLFcgy1gpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Currency = _t, #"Exchange Rate" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Currency", type text}, {"Exchange Rate", Int64.Type}})
in
    #"Changed Type"

     

    Combined_for_GL_Check:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSBUy6KsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Entered Currency" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entered Currency", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Rate", each let c = [Entered Currency]
in Table.SelectRows(Exchange_Rates,each [Currency] = 
c)),
    #"Expanded Rate" = Table.ExpandTableColumn(#"Added Custom", "Rate", {"Exchange Rate"}, {"Rate.Exchange Rate"})
in
    #"Expanded Rate"

     

     

    Or we can create a calculated column to meet your requirement:

     

    Rate_Dax = LOOKUPVALUE('Exchange_Rates'[Exchange Rate],'Exchange_Rates'[Currency],[Entered Currency])

     

     
    Best regards,