Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 Mix | Year Range | Max Inception Year |
Archetype | 2001 | 2001 |
Archetype | 2002 | 2001 |
Archetype | 2003 | 2001 |
Archetype | 2004 | 2001 |
Archetype | 2005 | 2001 |
Archetype | 2006 | 2001 |
Archetype | 2007 | 2001 |
Archetype | 2008 | 2001 |
Archetype | 2009 | 2001 |
Archetype | 2010 | 2010 |
Archetype | 2011 | 2010 |
Archetype | 2012 | 2012 |
Archetype | 2013 | 2012 |
Archetype | 2014 | 2012 |
Archetype | 2015 | 2012 |
Archetype | 2016 | 2012 |
Archetype | 2017 | 2012 |
Archetype | 2018 | 2012 |
Archetype | 2019 | 2012 |
Archetype | 2020 | 2012 |
Archetype | 2021 | 2012 |
Archetype | 2022 | 2012 |
Archetype | 2023 | 2012 |
Archetype | 2024 | 2012 |
Archetype | 2000 | 2000 |
Bond Bias Prospective | 2003 | 2003 |
Bond Bias Prospective | 2004 | 2003 |
Bond Bias Prospective | 2005 | 2003 |
Bond Bias Prospective | 2006 | 2003 |
Bond Bias Prospective | 2007 | 2003 |
Bond Bias Prospective | 2008 | 2003 |
Bond Bias Prospective | 2009 | 2003 |
Bond Bias Prospective | 2010 | 2010 |
Bond Bias Prospective | 2011 | 2010 |
Bond Bias Prospective | 2012 | 2010 |
Bond Bias Prospective | 2013 | 2010 |
Bond Bias Prospective | 2014 | 2010 |
Bond Bias Prospective | 2015 | 2010 |
Bond Bias Prospective | 2016 | 2010 |
Bond Bias Prospective | 2017 | 2010 |
Bond Bias Prospective | 2018 | 2010 |
Bond Bias Prospective | 2019 | 2010 |
Bond Bias Prospective | 2020 | 2010 |
Bond Bias Prospective | 2021 | 2010 |
Bond Bias Prospective | 2022 | 2010 |
Bond Bias Prospective | 2023 | 2010 |
Bond Bias Prospective | 2024 | 2010 |
Bond Bias Prospective | 2000 | 2000 |
Bond Bias Prospective | 2001 | 2001 |
Bond Bias Prospective | 2002 | 2001 |
Defensive Max Prospective | 2000 | 2000 |
Defensive Max Prospective | 2001 | 2001 |
Defensive Max Prospective | 2002 | 2001 |
Defensive Max Prospective | 2003 | 2001 |
Defensive Max Prospective | 2004 | 2001 |
Defensive Max Prospective | 2005 | 2001 |
Defensive Max Prospective | 2006 | 2001 |
Defensive Max Prospective | 2007 | 2001 |
Defensive Max Prospective | 2008 | 2001 |
Defensive Max Prospective | 2009 | 2001 |
Defensive Max Prospective | 2010 | 2010 |
Defensive Max Prospective | 2011 | 2010 |
Defensive Max Prospective | 2012 | 2010 |
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"
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"
Proud to be a Super User! | |
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.
Name | Ticker | Inception Year | Archetype | Top Heavy | Defensive Propsective | Defensive Plus Propsective | Defensive Max Prospective | Bond Bias Prospective |
Russell 1000 Growth | ^RLG | 2002 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
Russell 1000 Value | ^RLV | 2002 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
Russell 2000 | ^RUT | 1987 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
Russell 2000 Growth | ^RUO | 2002 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
Russell 2000 Value | ^RUJ | 2002 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
S&P 500 | ^GSPC | 1927 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
Bloomberg Barclays Agg | AGG | 2003 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 40.00% |
13 Week Treasury Bill | ^IRX | 1960 | 0.00% | 0.00% | 20.00% | 30.00% | 50.00% | 0.00% |
Treasury Yield 5 Year | ^FVX | 1962 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
CBOE Interest Rate 10 Year T No | ^TNX | 1962 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
Treasury Yield 30 Year | ^TYX | 1977 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
Vanguard S&P 500 Index Fund | VOO | 2010 | 30.00% | 12.00% | 40.00% | 38.00% | 25.00% | 40.00% |
iShare Core S&P 500 | IVV | 2000 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
iShare Russell 1000 Growth | IWF | 2000 | 10.00% | 7.00% | 15.00% | 12.25% | 10.00% | 10.00% |
iShare Russell 1000 Value | IWD | 2000 | 10.00% | 15.00% | 15.00% | 12.25% | 10.00% | 5.00% |
iShare Russell 2000 | IWM | 2000 | 5.00% | 7.00% | 0.00% | 0.00% | 0.00% | 0.00% |
iShare Russell 2000 Growth | IWO | 2000 | 5.00% | 7.00% | 0.00% | 0.00% | 0.00% | 0.00% |
iShare Russell 2000 Value | IWN | 2000 | 10.00% | 15.00% | 0.00% | 0.00% | 0.00% | 0.00% |
iShare Russell Midcap | IWR | 2001 | 12.00% | 15.00% | 10.00% | 7.50% | 5.00% | 5.00% |
iShare MSCI Emerging | EEM | 2003 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
iShare Core MSCI Emerging | IEMG | 2012 | 8.00% | 15.00% | 0.00% | 0.00% | 0.00% | 0.00% |
iShare MSCI EAFE | EFA | 2001 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
iShare Core MSCI EAFE | IEFA | 2012 | 10.00% | 7.00% | 0.00% | 0.00% | 0.00% | 0.00% |
BitCoin | BTC | 2014 | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
Ethereum | ETH | 2017 | 0.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"
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"
Proud to be a Super User! | |
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.
Name | Year Range | Inception Year |
Archetype | 2012 | 2012 |
Archetype | 2013 | 2012 |
Archetype | 2014 | 2012 |
Archetype | 2015 | 2012 |
Archetype | 2016 | 2012 |
Archetype | 2017 | 2012 |
Archetype | 2018 | 2012 |
Archetype | 2019 | 2012 |
Archetype | 2020 | 2012 |
Archetype | 2021 | 2012 |
Archetype | 2022 | 2012 |
Archetype | 2023 | 2012 |
Archetype | 2024 | 2012 |
Archetype | 2012 | 2012 |
Archetype | 2013 | 2012 |
Archetype | 2014 | 2012 |
Archetype | 2015 | 2012 |
Archetype | 2016 | 2012 |
Archetype | 2017 | 2012 |
Archetype | 2018 | 2012 |
Archetype | 2019 | 2012 |
Archetype | 2020 | 2012 |
Archetype | 2021 | 2012 |
Archetype | 2022 | 2012 |
Archetype | 2023 | 2012 |
Archetype | 2024 | 2012 |
Bond Bias Prospective | 2010 | 2010 |
Bond Bias Prospective | 2011 | 2010 |
Bond Bias Prospective | 2012 | 2010 |
Bond Bias Prospective | 2013 | 2010 |
Bond Bias Prospective | 2014 | 2010 |
Bond Bias Prospective | 2015 | 2010 |
Bond Bias Prospective | 2016 | 2010 |
Bond Bias Prospective | 2017 | 2010 |
Bond Bias Prospective | 2018 | 2010 |
Bond Bias Prospective | 2019 | 2010 |
Bond Bias Prospective | 2020 | 2010 |
Bond Bias Prospective | 2021 | 2010 |
Bond Bias Prospective | 2022 | 2010 |
Bond Bias Prospective | 2023 | 2010 |
Bond Bias Prospective | 2024 | 2010 |
Defensive Max Prospective | 2010 | 2010 |
Defensive Max Prospective | 2011 | 2010 |
Defensive Max Prospective | 2012 | 2010 |
Defensive Max Prospective | 2013 | 2010 |
Defensive Max Prospective | 2014 | 2010 |
Defensive Max Prospective | 2015 | 2010 |
Defensive Max Prospective | 2016 | 2010 |
Defensive Max Prospective | 2017 | 2010 |
Defensive Max Prospective | 2018 | 2010 |
Defensive Max Prospective | 2019 | 2010 |
Defensive Max Prospective | 2020 | 2010 |
Defensive Max Prospective | 2021 | 2010 |
Defensive Max Prospective | 2022 | 2010 |
Defensive Max Prospective | 2023 | 2010 |
Defensive Max Prospective | 2024 | 2010 |
Defensive Plus Propsective | 2010 | 2010 |
Defensive Plus Propsective | 2011 | 2010 |
Defensive Plus Propsective | 2012 | 2010 |
Defensive Plus Propsective | 2013 | 2010 |
Defensive Plus Propsective | 2014 | 2010 |
Defensive Plus Propsective | 2015 | 2010 |
Defensive Plus Propsective | 2016 | 2010 |
Defensive Plus Propsective | 2017 | 2010 |
Defensive Plus Propsective | 2018 | 2010 |
Defensive Plus Propsective | 2019 | 2010 |
Defensive Plus Propsective | 2020 | 2010 |
Defensive Plus Propsective | 2021 | 2010 |
Defensive Plus Propsective | 2022 | 2010 |
Defensive Plus Propsective | 2023 | 2010 |
Defensive Plus Propsective | 2024 | 2010 |
Defensive Propsective | 2010 | 2010 |
Defensive Propsective | 2011 | 2010 |
Defensive Propsective | 2012 | 2010 |
Defensive Propsective | 2013 | 2010 |
Defensive Propsective | 2014 | 2010 |
Defensive Propsective | 2015 | 2010 |
Defensive Propsective | 2016 | 2010 |
Defensive Propsective | 2017 | 2010 |
Defensive Propsective | 2018 | 2010 |
Defensive Propsective | 2019 | 2010 |
Defensive Propsective | 2020 | 2010 |
Defensive Propsective | 2021 | 2010 |
Defensive Propsective | 2022 | 2010 |
Defensive Propsective | 2023 | 2010 |
Defensive Propsective | 2024 | 2010 |
Top Heavy | 2012 | 2012 |
Top Heavy | 2013 | 2012 |
Top Heavy | 2014 | 2012 |
Top Heavy | 2015 | 2012 |
Top Heavy | 2016 | 2012 |
Top Heavy | 2017 | 2012 |
Top Heavy | 2018 | 2012 |
Top Heavy | 2019 | 2012 |
Top Heavy | 2020 | 2012 |
Top Heavy | 2021 | 2012 |
Top Heavy | 2022 | 2012 |
Top Heavy | 2023 | 2012 |
Top Heavy | 2024 | 2012 |
Top Heavy | 2012 | 2012 |
Top Heavy | 2013 | 2012 |
Top Heavy | 2014 | 2012 |
Top Heavy | 2015 | 2012 |
Top Heavy | 2016 | 2012 |
Top Heavy | 2017 | 2012 |
Top Heavy | 2018 | 2012 |
Top Heavy | 2019 | 2012 |
Top Heavy | 2020 | 2012 |
Top Heavy | 2021 | 2012 |
Top Heavy | 2022 | 2012 |
Top Heavy | 2023 | 2012 |
Top Heavy | 2024 | 2012 |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
61 | |
53 | |
27 | |
16 | |
10 |