Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Referencing a value in the same row but in dynamic columns

Hey there, 

 

my tables looks like this:

Sales in EURUSDSEKPLNCADAUDCurrency Code
22.911.113.24.561.091.2EUR
9.121.043.14.551.130.98PLN
7.131.093.34.61.071.1SEK

 

The columns USD, SEK, PLN, CAD, AUD refer to exchange rates related to EUR (just example inputs).

I want to achieve a new column, that calculates the Sales in EUR in the respective currency displayed in the column Currency Code.

 

My problem is: How do I reference for example the column SEK when the Currency Code is SEK?
My power query formula so far is - missing the appropriate column name (????): 

 

if [Currency Code] <> "EUR" then Value.Multiply([Sales in EUR], ????) else [Sales in EUR]

 

 

 

Thanks for your help.

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    4 years ago

    You can use the Record.Field method:

     

     

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales in EUR", type number}, {"USD", type number}, {"SEK", type number}, {"PLN", type number}, {"CAD", type number}, {"AUD", type number}, {"Currency Code", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Sales in Currency Code", each  
        if [Currency Code] = "EUR" 
            then [Sales in EUR] 
            else Record.Field(_, [Currency Code]) * [Sales in EUR] , Currency.Type)
in
    #"Added Custom"

     

     

     

     

     

4 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    4 years ago

    You can use the Record.Field method:

     

     

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales in EUR", type number}, {"USD", type number}, {"SEK", type number}, {"PLN", type number}, {"CAD", type number}, {"AUD", type number}, {"Currency Code", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Sales in Currency Code", each  
        if [Currency Code] = "EUR" 
            then [Sales in EUR] 
            else Record.Field(_, [Currency Code]) * [Sales in EUR] , Currency.Type)
in
    #"Added Custom"

     

     

     

     

     

      • ronrsnfld's avatar
        ronrsnfld
        Icon for Super User rankSuper User
        4 years ago

        Thanks.

        I've been slowly learning M Code and finding there is a lot that can be done not apparent in the UI. But I don't have a feeling about what is the most efficient way of doing things.  I suppose that will come with time.

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous ,

     

    Try the following code. I haven't re-pivoted the data as the format it ends up in is the correct structure for data model reporting. Also note that I added a EUR column with a factor of 1 to simplify the process.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY1BCoAwDAT/knMJTdpa8wBPiojiqfT/3zBNRA/ZgYXZtAbMKAQB7JAGErJmxjJZF8UwuuU+oYcGgsSfE7M55E7xnaSIKLPi2HaTqrf0jyZMJr1/qqua17JC7w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales in EUR" = _t, EUR = _t, USD = _t, SEK = _t, PLN = _t, CAD = _t, AUD = _t, #"Currency Code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales in EUR", type number}, {"USD", type number}, {"SEK", type number}, {"PLN", type number}, {"CAD", type number}, {"AUD", type number}, {"Currency Code", type text}, {"EUR", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Sales in EUR", "Currency Code"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "salesInCurrencyCode", each if [Currency Code] = [Attribute] then Number.From([Currency Code] = [Attribute]) * [Sales in EUR] * [Value] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([salesInCurrencyCode] <> null))
in
    #"Filtered Rows"

     

     

    Pete