topic Re: want to get top n number of columns based on the highest to lowest value in Power Query

want to get top n number of columns based on the highest to lowest value

muhammadfaisal — Fri, 30 Jun 2023 14:24:23 GMT

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

Re: want to get top n number of columns based on the highest to lowest value

AlienSx — Fri, 30 Jun 2023 15:41:31 GMT

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.

Re: want to get top n number of columns based on the highest to lowest value

muhammadfaisal — Fri, 30 Jun 2023 16:37:28 GMT

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

Re: want to get top n number of columns based on the highest to lowest value

muhammadfaisal — Fri, 30 Jun 2023 16:40:45 GMT

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

Re: want to get top n number of columns based on the highest to lowest value

muhammadfaisal — Fri, 30 Jun 2023 16:45:47 GMT

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

Re: want to get top n number of columns based on the highest to lowest value

AlienSx — Sat, 01 Jul 2023 05:30:40 GMT

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

Re: want to get top n number of columns based on the highest to lowest value

muhammadfaisal — Sat, 01 Jul 2023 20:24:05 GMT

AlienSx thanks for your helps. it just worked the way i wanted. Kudos!

Re: want to get top n number of columns based on the highest to lowest value

dufoq3 — Mon, 12 Feb 2024 15:00:24 GMT

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"