Forum Discussion
Divide every row with a specific row
You can get the value by first using Table.SelectRows (you don't need to write this yourself, you can filter from the column header to get the right syntax).
This returns a table. You then put the name of the column after it like TableSelectRows(...........[Rate]
This will return a column. Now, even though it's a single column from a single row, we need to get the first value with List.First()
That's one algorithm to do it and you may need to put part of it in a variable to multiply each row by it.
See how you get on.
You can get the value by first using Table.SelectRows (you don't need to write this yourself, you can filter from the column header to get the right syntax).
This returns a table. You then put the name of the column after it like TableSelectRows(...........[Rate]
This will return a column. Now, even though it's a single column from a single row, we need to get the first value with List.First()
That's one algorithm to do it and you may need to put part of it in a variable to multiply each row by it.
See how you get on.
I got it to work with your logic, although i guess I used quite a bit more steps than necessary
let
Source = Xml.Tables(Web.Contents("https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml?c57b0ed41402673d8e5e11424e48bee2")),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"subject", type text}}),
#"http://www ecb int/vocabulary/2002-08-01/eurofxref" = #"Changed Type"{0}[#"http://www.ecb.int/vocabulary/2002-08-01/eurofxref"],
Cube = #"http://www ecb int/vocabulary/2002-08-01/eurofxref"{0}[Cube],
Cube1 = Cube{0}[Cube],
#"Changed Type1" = Table.TransformColumnTypes(Cube1,{{"Attribute:time", type date}}),
Cube2 = #"Changed Type1"{0}[Cube],
#"Replaced Value" = Table.ReplaceValue(Cube2,".",",",Replacer.ReplaceText,{"Attribute:rate"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"Attribute:rate", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", "From", each "EUR"),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Attribute:currency", "To"}, {"Attribute:rate", "Rate"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Rate", "From", "To"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Reordered Columns",{{"From", type text}}),
#"Insert Rows" = Table.InsertRows(#"Changed Type3",0,{[Rate = 1, From = "EUR", To = "EUR"]}),
#"Custom Function" = Table.AddColumn(#"Insert Rows","RateInverse", each Table.SelectRows(#"Insert Rows",each [To]="DKK")),
#"Expanded RateInverse" = Table.ExpandTableColumn(#"Custom Function", "RateInverse", {"Rate"}, {"Rate.1"}),
#"Added Custom1" = Table.AddColumn(#"Expanded RateInverse", "RateDKK", each [Rate]/[Rate.1]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Rate", "Rate.1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Removed Columns","EUR","DKK",Replacer.ReplaceText,{"From"}),
#"Added Custom2" = Table.AddColumn(#"Replaced Value1", "Custom", each 1/[RateDKK]),
#"Renamed Columns2" = Table.RenameColumns(#"Added Custom2",{{"RateDKK", "Rate"}, {"Custom", "RateInverse"}}),
#"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns2",{{"RateInverse", type number}, {"Rate", type number}})
in
#"Changed Type4"
