Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Very much a novice here so apologies if this query seems a bit basic.
I have a table where one of the columns holds nested tables:
Financial Year | All Rows |
2021/22 | Table |
2022/23 | Table |
2023/24 | Table |
The nested tables hold a large number of columns and rows which will be manipulated after they're expanded but before that, I need to filter each table to contain only the records that match the latest [Source.DateCreated]. This date would be different for each [Financial Year].
Example of Nested Table contents:
Source.DateCreated | Forecast Sales | Team |
1/3/2021 | 564 | Research |
3/3/2021 | 657 | Research |
3/3/2021 | 200 | Research |
3/3/2021 | 354 | Manufacturing |
2/3/2021 | 784 | Manufacturing |
My desired result for the above example:
Source.DateCreated | Forecast Sales | Team |
3/3/2021 | 657 | Research |
3/3/2021 | 200 | Research |
3/3/2021 | 354 | Manufacturing |
I know that I can filter by the latest date for a table but I'm clueless on how to do this for a nested table.
Does anyone know how I would go about doing this?
Solved! Go to Solution.
you should use the add.column function using some criteria like:
Table.SelectRows([#"All Rows"], let latest = List.Max([#"All Rows"][Source.DateCreated]) in each [Source.DateCreated] = latest)
you should use the add.column function using some criteria like:
Table.SelectRows([#"All Rows"], let latest = List.Max([#"All Rows"][Source.DateCreated]) in each [Source.DateCreated] = latest)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.