Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!