Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

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

 

 

 

@ronrsnfld  pretty neat

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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 @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

 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors