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
bigreduw64
New Member

LIst Max.N returning null table

I have the following set up and in #"Filtered Rows" I am trying to get the top 4 values returned. With the code I have below, it is returning an empty table and I haven't been able to figure out what is broken.  Any guidance would be greatly appreciated.  Thank you!

let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Fiscal Year Week", type number}, {"Customer Number", Int64.Type}, {"PIM Superclass", type text}, {"Sales $", type number}, {"LY", type number}, {"Var", type number}, {"Var %", type number}, {"AOP", type any}, {"Var2", type any}, {"Cases", type number}, {"LY3", type number}, {"Var4", type number}, {"Var %5", type number}, {"AOP6", type any}, {"Var7", type any}, {"Gross TGP", type number}, {"LY8", type number}, {"Var9", type number}, {"Var %10", type number}, {"AOP11", type any}, {"Var12", type any}, {"Gross TGP %", type number}, {"LY13", type number}, {"Var14", type number}, {"AOP15", type any}, {"Var16", type any}, {"SAGP", type number}, {"LY17", type number}, {"Var18", type number}, {" Var %", type number}, {"SAGP %", type number}, {"LY19", type number}, {"Var20", type number}, {"Net Accounts", Int64.Type}, {"LY21", Int64.Type}, {"Var22", Int64.Type}, {"Var %23", Int64.Type}, {"Gross TGP+PA per Drop", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Fiscal Year Week", "Customer Number"}, {{"Sales $", each List.Sum([#"Sales $"]), type nullable number}, {"Cases", each List.Sum([Cases]), type nullable number}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Fiscal Year Week] = List.MaxN(#"Grouped Rows"[Fiscal Year Week],4))),
#"Grouped Rows1" = Table.Group(#"Filtered Rows", {"Customer Number"}, {{"Sales $", each List.Sum([#"Sales $"]), type nullable number}, {"Cases", each List.Sum([Cases]), type nullable number}}),
#"Divided Column" = Table.TransformColumns(#"Grouped Rows1", {{"Sales $", each _ / 4, type number}}),
#"Divided Column1" = Table.TransformColumns(#"Divided Column", {{"Cases", each _ / 4, type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Divided Column1",{{"Sales $", "4 Week Avg Weekly Sales $"}, {"Cases", "4 Week Avg Weekly Cases"}})
in
#"Renamed Columns"

2 REPLIES 2
amitchandak
Super User
Super User

@amitchandak I looked through this and not sure that it will work for my needs as I am trying to do this for a dynamic data set that won't always have the the same max values in the [Fiscal Year Week] column.  

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.