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

[muhammadfaisal]

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

[dufoq3]

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"

[muhammadfaisal]

@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

[AlienSx]

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. 

[muhammadfaisal]

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

[AlienSx · Accepted 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

[muhammadfaisal]

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

[muhammadfaisal]

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