cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerQuestions
New Member

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.

1 ACCEPTED SOLUTION
ronrsnfld
Resolver IV
Resolver IV

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_0-1634323740648.png

 

 

 

View solution in original post

4 REPLIES 4
ronrsnfld
Resolver IV
Resolver IV

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_0-1634323740648.png

 

 

 

View solution in original post

@ronrsnfld  pretty neat





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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
Super User
Super User

Hi @PowerQuestions ,

 

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

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors