Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Referencing a value in the same row but in dynamic columns

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 ...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    4 years ago

    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"

     

     

     

     

     

BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
4 years ago

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