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! |  |
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! | |
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
We want your feedback!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
A Year in Review - December 2024
Find out what content was popular in the Fabric community during 2024.
