cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper III

## Divide every row with a specific row

I have a big exchange rate table, unfortunately all the numbers are from EUR to XXX currency.

I would like to alter it to be from DKK to XXX currency, and would be simple just to divide all the Rates by the Rate that is in the DKK row.

So how do i divide all the 31 rows af "Rate" with the value in row 5 (Also given that it might not always be row 5, but always be the row with To = "DKK" value?

I found a solution using merge queries and some inverse logic with joins, but seems super convoluted for something so simple.

1 ACCEPTED SOLUTION
Super User

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.

2 REPLIES 2
Super User

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.

Helper III

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}}),
#"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"}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Rate", "Rate.1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Removed Columns","EUR","DKK",Replacer.ReplaceText,{"From"}),
#"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"

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors