Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
muhammadfaisal
New Member

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

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

 

1 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

 

 

View solution in original post

7 REPLIES 7
dufoq3
Super User
Super User

Hi @muhammadfaisal,

 

This code shows TOP5 of all time. If you want to show TOP5 of every year - let me know.

 

Result:

dufoq3_0-1707749906790.png

 

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"

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

muhammadfaisal
New Member

@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
Super User
Super User

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. 

 

dateARSAUDBHDBWPBRLBND 
6/27/2023255.15651.4966220.37613.367984.8043741.348996 
6/26/2023253.53731.4967830.37613.394294.7698871.353372 
6/26/2023252.37881.4981070.37613.396214.7669841.352252 
6/25/2023253.08081.4967560.37613.323874.7871641.35193 
6/25/2023253.08071.4976430.37613.323874.7871631.35193 
6/24/2023253.03891.4971030.37613.323874.7845861.350962 
6/24/2023253.03561.4963230.37613.323864.7851481.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

 

 

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

 

dateARSAUDBHDBWPBRLBND
6/27/2023255.15651.4966220.37613.367984.8043741.348996
6/26/2023253.53731.4967830.37613.394294.7698871.353372
6/26/2023252.37881.4981070.37613.396214.7669841.352252
6/25/2023253.08081.4967560.37613.323874.7871641.35193
6/25/2023253.08071.4976430.37613.323874.7871631.35193
6/24/2023253.03891.4971030.37613.323874.7845861.350962
6/24/2023253.03561.4963230.37613.323864.7851481.351308

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors