Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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"
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 pretty neat
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.
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
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.