March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi folks
i will be really grateful if someone can help me with a problem. I have a csv file (exchanges.csv) that contatins total 55 columns. first column is date and other 54 columns are actually currency codes of different countries. currency columns have numeric data for each date.
i want to write a code in power query to show top 5 currency codes based on the top 5 highest values among all 54 currencies for each calendar year. Also i want to see the maximum value based on which currency qualified for top 5. so there will be three columns.
Column 1: Year
Column 2: Currecny code
Column 3: Actual data value (maximum value)
Thanks
Solved! Go to Solution.
@muhammadfaisal try this
let
Source = your_table,
types = Table.TransformColumnTypes(Source,{{"date", type date}}),
sort = Table.Sort(types,{{"date", Order.Ascending}}),
// list of currency codes
ccy_lst = List.Buffer(List.Skip(Table.ColumnNames(sort))),
// this is a list of ccy & List.Max combinations to use in grouping later
find_max = List.Transform(ccy_lst, (x) => {x, (y) => List.Max(Table.Column(y, x))}),
// group data by year and find max of each currency value (rate?)
group_by_year =
Table.Group(
sort, "date",
find_max,
GroupKind.Local,
(s, c) => Number.From(Date.Year(s) <> Date.Year(c))
),
// bring ccy columns to single column to group by year later
unpivot = Table.UnpivotOtherColumns(group_by_year, {"date"}, "Ccy", "Max Value"),
get_year = Table.TransformColumns(unpivot, {"date", Date.Year}),
// group by year, sort by value and get top 5
group_again = Table.Group(get_year, {"date"}, {{"top5", each Table.FirstN(Table.Sort(_, {"Max Value", Order.Descending}), 5)}}),
expand = Table.ExpandTableColumn(group_again, "top5", {"Ccy", "Max Value"}, {"Ccy", "Max Value"}),
rename = Table.RenameColumns(expand, {"date", "Year"})
in
rename
Hi @muhammadfaisal,
This code shows TOP5 of all time. If you want to show TOP5 of every year - let me know.
Result:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdBLDoMwDATQu7BGIfH4exbE/a9RlyZtodCdFcVPM17XSReyhSphmicSKU1UcmyFQ5Uox1pg+nxCgVp4jly8Moz3j2CP0Gmbd0w/GIrAMDBznLBgih0zDXd7YQIYXWCUi+4d81btjCm1jqXWkwnl4sDkO1n16u9koieMsMdJzK3pwFrgzrJumfK55dHCr8UHCx7DavWPxeLarZrdbzDRURJ0gWnHpLGPYMjDbNsD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, ARS = _t, AUD = _t, BHD = _t, BWP = _t, BRL = _t, BND = _t]),
ChangedTypesDynamic = Table.TransformColumns(Source,
//Dates
List.Transform(
List.Select(Table.ColumnNames(Source), each Text.Contains(_, "date", Comparer.OrdinalIgnoreCase)),
(colName)=> { colName, each Date.From(_, "en-US"), type date }
) &
//Currencies
List.Transform(
List.Select(Table.ColumnNames(Source), each not Text.Contains(_, "date", Comparer.OrdinalIgnoreCase)),
(colName)=> { colName, each Number.From(_, "en-US"), type number }
)
),
#"Extracted Year" = Table.TransformColumns(ChangedTypesDynamic,{{"date", Date.Year, Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Year",{{"date", "Year"}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Year"}, "Currency", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Year", "Currency"}, {{"Max Value", each List.Max([Value]), type number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Max Value", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",5)
in
#"Kept First Rows"
@AlienSx i do have this query which is showing grouped data on year basis with maximum value. the only problem is that it is not picking currency column based on the maximum value. rather it is only showing top 5 on alphabetical order.
let
Source = exchanges, // Replace "exchanges" with the actual name of your loaded table
// Add a custom column with the calendar year
CustomYear = Table.AddColumn(Source, "Year", each Date.Year([date])),
// Group the data by calendar year and get the names of top 5 columns with maximum values
GroupedData = Table.Group(CustomYear, {"Year"},
{{"TopColumns", each List.FirstN(List.Sort(Table.ColumnNames(Table.RemoveColumns(_, {"date"}))), 5), type list}}
),
// Expand the TopColumns list to individual columns
ExpandedColumns = Table.ExpandListColumn(GroupedData, "TopColumns"),
// Select the calendar year and the top 5 columns from the original table
SelectedColumns = Table.SelectColumns(ExpandedColumns, {"Year", "TopColumns"}),
// Retrieve the maximum value for each column in the corresponding calendar year
MaxValues = Table.AddColumn(SelectedColumns, "MaxValue", each List.Max(Table.Column(Source, [TopColumns]))),
// Reorder the columns for better visibility (Year, Column Name, Max Value)
ReorderedColumns = Table.ReorderColumns(MaxValues, {"Year", "TopColumns", "MaxValue"})
in
ReorderedColumns
hi, @muhammadfaisal please show your data. Don't need to show all 55 columns. 3-4 columns and 3-4 rows is enough. It's not clear if any single row represents a summary of all year or just a one of the dates of that year.
date | ARS | AUD | BHD | BWP | BRL | BND | |
6/27/2023 | 255.1565 | 1.496622 | 0.376 | 13.36798 | 4.804374 | 1.348996 | |
6/26/2023 | 253.5373 | 1.496783 | 0.376 | 13.39429 | 4.769887 | 1.353372 | |
6/26/2023 | 252.3788 | 1.498107 | 0.376 | 13.39621 | 4.766984 | 1.352252 | |
6/25/2023 | 253.0808 | 1.496756 | 0.376 | 13.32387 | 4.787164 | 1.35193 | |
6/25/2023 | 253.0807 | 1.497643 | 0.376 | 13.32387 | 4.787163 | 1.35193 | |
6/24/2023 | 253.0389 | 1.497103 | 0.376 | 13.32387 | 4.784586 | 1.350962 | |
6/24/2023 | 253.0356 | 1.496323 | 0.376 | 13.32386 | 4.785148 | 1.351308 |
@muhammadfaisal try this
let
Source = your_table,
types = Table.TransformColumnTypes(Source,{{"date", type date}}),
sort = Table.Sort(types,{{"date", Order.Ascending}}),
// list of currency codes
ccy_lst = List.Buffer(List.Skip(Table.ColumnNames(sort))),
// this is a list of ccy & List.Max combinations to use in grouping later
find_max = List.Transform(ccy_lst, (x) => {x, (y) => List.Max(Table.Column(y, x))}),
// group data by year and find max of each currency value (rate?)
group_by_year =
Table.Group(
sort, "date",
find_max,
GroupKind.Local,
(s, c) => Number.From(Date.Year(s) <> Date.Year(c))
),
// bring ccy columns to single column to group by year later
unpivot = Table.UnpivotOtherColumns(group_by_year, {"date"}, "Ccy", "Max Value"),
get_year = Table.TransformColumns(unpivot, {"date", Date.Year}),
// group by year, sort by value and get top 5
group_again = Table.Group(get_year, {"date"}, {{"top5", each Table.FirstN(Table.Sort(_, {"Max Value", Order.Descending}), 5)}}),
expand = Table.ExpandTableColumn(group_again, "top5", {"Ccy", "Max Value"}, {"Ccy", "Max Value"}),
rename = Table.RenameColumns(expand, {"date", "Year"})
in
rename
date | ARS | AUD | BHD | BWP | BRL | BND |
6/27/2023 | 255.1565 | 1.496622 | 0.376 | 13.36798 | 4.804374 | 1.348996 |
6/26/2023 | 253.5373 | 1.496783 | 0.376 | 13.39429 | 4.769887 | 1.353372 |
6/26/2023 | 252.3788 | 1.498107 | 0.376 | 13.39621 | 4.766984 | 1.352252 |
6/25/2023 | 253.0808 | 1.496756 | 0.376 | 13.32387 | 4.787164 | 1.35193 |
6/25/2023 | 253.0807 | 1.497643 | 0.376 | 13.32387 | 4.787163 | 1.35193 |
6/24/2023 | 253.0389 | 1.497103 | 0.376 | 13.32387 | 4.784586 | 1.350962 |
6/24/2023 | 253.0356 | 1.496323 | 0.376 | 13.32386 | 4.785148 | 1.351308 |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
20 | |
12 | |
10 | |
10 | |
7 |
User | Count |
---|---|
43 | |
26 | |
16 | |
16 | |
11 |