Referencing a value in the same row but in dynamic columns

[Anonymous]

Hey there, 

my tables looks like this:

Sales in EUR	USD	SEK	PLN	CAD	AUD	Currency Code
22.91	1.11	3.2	4.56	1.09	1.2	EUR
9.12	1.04	3.1	4.55	1.13	0.98	PLN
7.13	1.09	3.3	4.6	1.07	1.1	SEK

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.

[ronrsnfld · Accepted solution]

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"

[smpa01]

@ronrsnfld  pretty neat

[ronrsnfld]

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]

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