Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
shoof
Regular Visitor

How to Filter a Table Based on Categories in another Column of the same table?

‎02-21-2024 11:02 AM

So I have this table below.  I want to filter it so that only the maximum Inception Year for each Investment Mix category is showing.  So for the investment mix Archetype only values with an Inception year of 2012 will show (because it's the latest year for that category), for Bond Bias only values with an inception year of 2010 will show.  How can I achive this?  Also, I am using Excel Power Query and can post the M Code for the table if needed.  Thanks in advance.

Investment MixYear RangeMax Inception Year
Archetype20012001
Archetype20022001
Archetype20032001
Archetype20042001
Archetype20052001
Archetype20062001
Archetype20072001
Archetype20082001
Archetype20092001
Archetype20102010
Archetype20112010
Archetype20122012
Archetype20132012
Archetype20142012
Archetype20152012
Archetype20162012
Archetype20172012
Archetype20182012
Archetype20192012
Archetype20202012
Archetype20212012
Archetype20222012
Archetype20232012
Archetype20242012
Archetype20002000
Bond Bias Prospective20032003
Bond Bias Prospective20042003
Bond Bias Prospective20052003
Bond Bias Prospective20062003
Bond Bias Prospective20072003
Bond Bias Prospective20082003
Bond Bias Prospective20092003
Bond Bias Prospective20102010
Bond Bias Prospective20112010
Bond Bias Prospective20122010
Bond Bias Prospective20132010
Bond Bias Prospective20142010
Bond Bias Prospective20152010
Bond Bias Prospective20162010
Bond Bias Prospective20172010
Bond Bias Prospective20182010
Bond Bias Prospective20192010
Bond Bias Prospective20202010
Bond Bias Prospective20212010
Bond Bias Prospective20222010
Bond Bias Prospective20232010
Bond Bias Prospective20242010
Bond Bias Prospective20002000
Bond Bias Prospective20012001
Bond Bias Prospective20022001
Defensive Max Prospective20002000
Defensive Max Prospective20012001
Defensive Max Prospective20022001
Defensive Max Prospective20032001
Defensive Max Prospective20042001
Defensive Max Prospective20052001
Defensive Max Prospective20062001
Defensive Max Prospective20072001
Defensive Max Prospective20082001
Defensive Max Prospective20092001
Defensive Max Prospective20102010
Defensive Max Prospective20112010
Defensive Max Prospective20122010
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
Labels:
Message 1 of 6
327 Views
0
5 REPLIES 5
shoof
Regular Visitor

‎02-22-2024 10:58 AM

Thanks for your Help!  I was able to get it to work.  It initially duplicated the Year Range for each Name.  This was the solution...

let
// Reference the existing table
Source = Custom_Mix_Tbl,

// Add your modifications below
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name", "Ticker", "Inception Year"}, "Attribute", "Value"),
#"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Value] <> 0)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value","Name","Ticker"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "Name"}}),
#"Grouped Rows1" = Table.Group(#"Renamed Columns", {"Name"}, {{"Inception Year", each List.Max([Inception Year]), type number}}),
#"Expanded Year Range" = Table.ExpandListColumn(Table.AddColumn(#"Grouped Rows1", "Year Range", each { [Inception Year] .. Date.Year(DateTime.LocalNow()) }), "Year Range"),
#"Sorted Rows1" = Table.Sort(#"Expanded Year Range",{{"Name", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows1",{"Inception Year"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Year Range", "Year"}})
in
#"Renamed Columns1"

Message 6 of 6
249 Views
jgeddes
Super User
Super User

‎02-21-2024 11:45 AM

You can try grouping by Investment Mix, choosing all rows and then selecting the rows in the resulting nested tables.
Here is an example...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldU9b8IwFIXhvxJlZrCvv0cQK1J3xIDACBZAJKrKv287RJVVODl38vA+SiJfJ9lu++XjcK7j8177RS/G2GnZLf41Ac2B5kELoEXQEmgZtPK+WTMtL5oFTablRXOgedACaBG0BFoGrbxvYkCzoIF9EbAvAvbFmGn5bavb9ditLvuh+3jchns9jJfP9jy6OedJF0gXSZdIl0lXONeeceAs6YR0jnSedIF0kXSJdJl0hXNCzkPIeQg5DyHnIeQ86Pey+b8A1/xr1vVUr8NP6zb7r5l7z1iruK7mGZzCeoUNChsVNilsVtjC2/ZbNGOtwv69A7tv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Investment Mix" = _t, #"Year Range" = _t, #"Max Inception Year" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Investment Mix", type text}, {"Year Range", Int64.Type}, {"Max Inception Year", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Investment Mix"}, {{"_nestedTable", each _, type table [Investment Mix=nullable text, Year Range=nullable number, Max Inception Year=nullable number]}}),
    fxSelectNestedRows = (inputTable as table) as table =>
    let
        source = inputTable,
        addMaxYear = Table.SelectRows(source, each [Max Inception Year] = List.Max(source[Max Inception Year]))
    in
        addMaxYear,
    
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"_nestedTable", each fxSelectNestedRows(_)}}),
    #"Expanded _nestedTable" = Table.ExpandTableColumn(Custom1, "_nestedTable", {"Year Range", "Max Inception Year"}, {"Year Range", "Max Inception Year"})
in
    #"Expanded _nestedTable"



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Message 2 of 6
293 Views
0
shoof
Regular Visitor
In response to jgeddes

‎02-22-2024 09:47 AM

Thanks, I was able to make this example work with a static table like I presented above.  However, I am starting with the table below and for some reason I am getting this error....Expression.Error: The column '_nestedTable' of the table wasn't found.  Details: _nestedTable

Below is the table I am starting with and my script.  The Custom1 function is where the error is appearing.

 

NameTickerInception YearArchetypeTop HeavyDefensive PropsectiveDefensive Plus PropsectiveDefensive Max ProspectiveBond Bias Prospective
Russell 1000 Growth^RLG20020.00%0.00%0.00%0.00%0.00%0.00%
Russell 1000 Value^RLV20020.00%0.00%0.00%0.00%0.00%0.00%
Russell 2000^RUT19870.00%0.00%0.00%0.00%0.00%0.00%
Russell 2000 Growth^RUO20020.00%0.00%0.00%0.00%0.00%0.00%
Russell 2000 Value^RUJ20020.00%0.00%0.00%0.00%0.00%0.00%
S&P 500^GSPC19270.00%0.00%0.00%0.00%0.00%0.00%
Bloomberg Barclays AggAGG20030.00%0.00%0.00%0.00%0.00%40.00%
13 Week Treasury Bill^IRX19600.00%0.00%20.00%30.00%50.00%0.00%
Treasury Yield 5 Year^FVX19620.00%0.00%0.00%0.00%0.00%0.00%
CBOE Interest Rate 10 Year T No^TNX19620.00%0.00%0.00%0.00%0.00%0.00%
Treasury Yield 30 Year^TYX19770.00%0.00%0.00%0.00%0.00%0.00%
Vanguard S&P 500 Index FundVOO201030.00%12.00%40.00%38.00%25.00%40.00%
iShare Core S&P 500IVV20000.00%0.00%0.00%0.00%0.00%0.00%
iShare Russell 1000 GrowthIWF200010.00%7.00%15.00%12.25%10.00%10.00%
iShare Russell 1000 ValueIWD200010.00%15.00%15.00%12.25%10.00%5.00%
iShare Russell 2000IWM20005.00%7.00%0.00%0.00%0.00%0.00%
iShare Russell 2000 GrowthIWO20005.00%7.00%0.00%0.00%0.00%0.00%
iShare Russell 2000 ValueIWN200010.00%15.00%0.00%0.00%0.00%0.00%
iShare Russell MidcapIWR200112.00%15.00%10.00%7.50%5.00%5.00%
iShare MSCI EmergingEEM20030.00%0.00%0.00%0.00%0.00%0.00%
iShare Core MSCI EmergingIEMG20128.00%15.00%0.00%0.00%0.00%0.00%
iShare MSCI EAFEEFA20010.00%0.00%0.00%0.00%0.00%0.00%
iShare Core MSCI EAFEIEFA201210.00%7.00%0.00%0.00%0.00%0.00%
BitCoinBTC20140.00%0.00%0.00%0.00%0.00%0.00%
EthereumETH20170.00%0.00%0.00%0.00%0.00%0.00%

 

let
// Reference the existing table
Source = Custom_Mix_Tbl,

// Add your modifications below
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name", "Ticker", "Inception Year"}, "Attribute", "Value"),
#"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Name", "Ticker"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> 0)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "Name"}}),
#"Expanded Year Range" = Table.ExpandListColumn(Table.AddColumn(#"Renamed Columns", "Year Range", each { [Inception Year] .. Date.Year(DateTime.LocalNow()) }), "Year Range"),
#"Grouped Rows" = Table.Group(#"Expanded Year Range", {"Name", "Year Range"}, {{"Max Inception Year", each List.Max([Inception Year]), Int64.Type}}),
#"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Name"}, {{"_nestedTable", each _, type table [Name=text, Year Range=number, Max Inception Year=number]}}),
fxSelectNestedRows = (inputTable as table) as table =>
let
source = inputTable,
addMaxYear = Table.SelectRows(source, each [Max Inception Year] = List.Max(source[Max Inception Year]))
in
addMaxYear,

Custom1 = Table.TransformColumns(#"Grouped Rows", {{"_nestedTable", each fxSelectNestedRows(_)}}),
#"Expanded _nestedTable" = Table.ExpandTableColumn(Custom1, "_nestedTable", {"Year Range", "Max Inception Year"}, {"Year Range", "Max Inception Year"})
in
#"Expanded _nestedTable"

Message 3 of 6
264 Views
0
jgeddes
Super User
Super User
In response to shoof

‎02-22-2024 10:12 AM

See if this gets you what you are looking for...

// Add your modifications below
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name", "Ticker", "Inception Year"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Name", "Ticker"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> 0)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "Name"}}),
    #"Expanded Year Range" = Table.ExpandListColumn(Table.AddColumn(#"Renamed Columns", "Year Range", each { [Inception Year] .. Date.Year(DateTime.LocalNow()) }), "Year Range"),
    #"Grouped Rows" = Table.Group(#"Expanded Year Range", {"Name"}, {{"_nestedTable", each _, type table}}),
    fxSelectNestedRows = (inputTable as table) as table =>
    let
    source = inputTable,
    addMaxYear = Table.SelectRows(source, each [Inception Year] = List.Max(source[Inception Year]))
    in
    addMaxYear,

    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"_nestedTable", each fxSelectNestedRows(_)}}),
    #"Expanded _nestedTable" = Table.ExpandTableColumn(Custom1, "_nestedTable", {"Year Range", "Inception Year", "Value"}, {"Year Range", "Inception Year", "Value"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded _nestedTable",{{"Inception Year", Int64.Type}, {"Value", Percentage.Type}, {"Year Range", Int64.Type}})
    in
    #"Changed Type2"



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Message 4 of 6
258 Views
0
shoof
Regular Visitor
In response to jgeddes

‎02-22-2024 10:25 AM

Wow, you are quick!  It's really close.  The only thing is it's listing each Name twice.  So for Archetype it's doing the Year Range from 2012-2024 twice on the list.  I looked at it and i'm not sure why.  All I really need is the Name and Year Range.

NameYear RangeInception Year
Archetype20122012
Archetype20132012
Archetype20142012
Archetype20152012
Archetype20162012
Archetype20172012
Archetype20182012
Archetype20192012
Archetype20202012
Archetype20212012
Archetype20222012
Archetype20232012
Archetype20242012
Archetype20122012
Archetype20132012
Archetype20142012
Archetype20152012
Archetype20162012
Archetype20172012
Archetype20182012
Archetype20192012
Archetype20202012
Archetype20212012
Archetype20222012
Archetype20232012
Archetype20242012
Bond Bias Prospective20102010
Bond Bias Prospective20112010
Bond Bias Prospective20122010
Bond Bias Prospective20132010
Bond Bias Prospective20142010
Bond Bias Prospective20152010
Bond Bias Prospective20162010
Bond Bias Prospective20172010
Bond Bias Prospective20182010
Bond Bias Prospective20192010
Bond Bias Prospective20202010
Bond Bias Prospective20212010
Bond Bias Prospective20222010
Bond Bias Prospective20232010
Bond Bias Prospective20242010
Defensive Max Prospective20102010
Defensive Max Prospective20112010
Defensive Max Prospective20122010
Defensive Max Prospective20132010
Defensive Max Prospective20142010
Defensive Max Prospective20152010
Defensive Max Prospective20162010
Defensive Max Prospective20172010
Defensive Max Prospective20182010
Defensive Max Prospective20192010
Defensive Max Prospective20202010
Defensive Max Prospective20212010
Defensive Max Prospective20222010
Defensive Max Prospective20232010
Defensive Max Prospective20242010
Defensive Plus Propsective20102010
Defensive Plus Propsective20112010
Defensive Plus Propsective20122010
Defensive Plus Propsective20132010
Defensive Plus Propsective20142010
Defensive Plus Propsective20152010
Defensive Plus Propsective20162010
Defensive Plus Propsective20172010
Defensive Plus Propsective20182010
Defensive Plus Propsective20192010
Defensive Plus Propsective20202010
Defensive Plus Propsective20212010
Defensive Plus Propsective20222010
Defensive Plus Propsective20232010
Defensive Plus Propsective20242010
Defensive Propsective20102010
Defensive Propsective20112010
Defensive Propsective20122010
Defensive Propsective20132010
Defensive Propsective20142010
Defensive Propsective20152010
Defensive Propsective20162010
Defensive Propsective20172010
Defensive Propsective20182010
Defensive Propsective20192010
Defensive Propsective20202010
Defensive Propsective20212010
Defensive Propsective20222010
Defensive Propsective20232010
Defensive Propsective20242010
Top Heavy20122012
Top Heavy20132012
Top Heavy20142012
Top Heavy20152012
Top Heavy20162012
Top Heavy20172012
Top Heavy20182012
Top Heavy20192012
Top Heavy20202012
Top Heavy20212012
Top Heavy20222012
Top Heavy20232012
Top Heavy20242012
Top Heavy20122012
Top Heavy20132012
Top Heavy20142012
Top Heavy20152012
Top Heavy20162012
Top Heavy20172012
Top Heavy20182012
Top Heavy20192012
Top Heavy20202012
Top Heavy20212012
Top Heavy20222012
Top Heavy20232012
Top Heavy20242012
Message 5 of 6
242 Views
0

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

View All
Top Solution Authors
View All