Hi @Anonymous ,
As much as I love Power Query, and I do love Power Query, I do not think it is the right place for this. DAX is much better at this type of analysls. However, I returned this:
Here is the code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZIxDsMgDEXvwpwI2xgDa7ceoFOUsWO33l/FDIiQBLp9rMe3v+VtM4/XimYxDi2gJSDIj+f3/VkpCwSzL5WhZIEODHJWCSeQOolvIe3mzk6xOtElpIxIzxzHxqjlNIFUMbTQee6iJIwhTSM8YXQkjuNsykSe52c/ZorydUnuPpqHFrpYkn7lsVGJFt3YKGizOGZEq75nuhvRiVL6Awr5kPYf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"BU Name" = _t, Date = _t, Item = _t, #"Sales €" = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-BM"),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Sales €", Int64.Type}}),
#"Grouped Rows" =
Table.Group(
#"Changed Type",
{"BU Name"},
{
{
"All Rows",
each
let
varTable = _
in
Table.AddColumn(
_,
"YTD Revenue",
each
let
varCurrentMonthEnd = Date.EndOfMonth([Date]),
varCurrentYear = Date.Year([Date])
in
List.Sum(
Table.SelectRows(
varTable,
each Date.Year([Date]) = varCurrentYear and [Date] <= varCurrentMonthEnd
)[#"Sales €"]
)
),
type table [BU Name=nullable text, Date=nullable date, Item=nullable text, #"Sales €"=nullable number, YTD Revenue = nullable number]
}
}
),
#"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Item", "YTD Revenue"}, {"Date", "Item", "YTD Revenue"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded All Rows",{"BU Name", "Date", "YTD Revenue"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
MonthlyTotal = Table.Group(#"Removed Duplicates", {"Date"}, {{"YTD Total", each List.Sum([YTD Revenue]), type nullable number}}),
PercentOfTotal =
Table.AddColumn(
#"Removed Duplicates",
"Percent of Total",
each
let
varCurrentMonth = [Date]
in
[YTD Revenue] /
List.Sum(
Table.SelectRows(
MonthlyTotal,
each [Date] = varCurrentMonth
)[YTD Total]
),
Percentage.Type
),
#"Removed Other Columns1" = Table.SelectColumns(PercentOfTotal,{"BU Name", "Date", "Percent of Total"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns1", List.Distinct(#"Removed Other Columns1"[#"BU Name"]), "BU Name", "Percent of Total", List.Sum)
in
#"Pivoted Column"I could probably spend some time optimizing it and reducing the steps, but it gets tedious dealing too much with nested lists, tables, and records.
If you are going to load this into the DAX Data model, I strongly suggest you get rid of the last pivot step and load it so it looks like this:
Then you can more easily filter the table or columns in DAX va having BU 1/2/3 in separate columns.
I make no claims on the performance on large data sets. I think I need to go back and do another Table.Group to ensure optimized performance, but after half an hour, my brain is a bit foggy on working with more embedded tables. 😂
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
Yeah, it generates the monthly totals in the MonthlyTotal step, then it has to do a Table.SelectRows (a filter) on that based on the current month. So with 5,000 rows, it is doing 5,000 Table.SelectRows. This is what Power Query is not good at.
However, try this. I did a grouping by month, then did the Table.SelectRows, then re-expanded. So it should cut down the filtering on a per month basis vs every single row.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZIxDsMgDEXvwpwI2xgDa7ceoFOUsWO33l/FDIiQBLp9rMe3v+VtM4/XimYxDi2gJSDIj+f3/VkpCwSzL5WhZIEODHJWCSeQOolvIe3mzk6xOtElpIxIzxzHxqjlNIFUMbTQee6iJIwhTSM8YXQkjuNsykSe52c/ZorydUnuPpqHFrpYkn7lsVGJFt3YKGizOGZEq75nuhvRiVL6Awr5kPYf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"BU Name" = _t, Date = _t, Item = _t, #"Sales €" = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-BM"),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Sales €", Int64.Type}}),
#"Grouped Rows" =
Table.Group(
#"Changed Type",
{"BU Name"},
{
{
"All Rows",
each
let
varTable = _
in
Table.AddColumn(
_,
"YTD Revenue",
each
let
varCurrentMonthEnd = Date.EndOfMonth([Date]),
varCurrentYear = Date.Year([Date])
in
List.Sum(
Table.SelectRows(
varTable,
each Date.Year([Date]) = varCurrentYear and [Date] <= varCurrentMonthEnd
)[#"Sales €"]
)
),
type table [BU Name=nullable text, Date=nullable date, Item=nullable text, #"Sales €"=nullable number, YTD Revenue = nullable number]
}
}
),
#"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Item", "YTD Revenue"}, {"Date", "Item", "YTD Revenue"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded All Rows",{"BU Name", "Date", "YTD Revenue"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
MonthlyTotal =
Table.Group(
#"Removed Duplicates",
{"Date"},
{
{"YTD Total", each List.Sum([YTD Revenue]), type nullable number}
}
),
#"Grouped Rows1" =
Table.Group(
#"Removed Duplicates",
{"Date"},
{
{
"All Rows",
each
let
varTable = _
in
Table.AddColumn(
_,
"Pct of Total",
each
let
varCurrentMonthEnd = Date.EndOfMonth([Date])
in
[YTD Revenue] /
Table.SelectRows(
MonthlyTotal,
each [Date] = varCurrentMonthEnd
)[YTD Total]{0}
),
type table [BU Name=nullable text, Date=nullable date, YTD Revenue=nullable number, Pct of Total=nullable Percentage.Type]}
}
),
#"Expanded All Rows1" = Table.ExpandTableColumn(#"Grouped Rows1", "All Rows", {"BU Name", "Pct of Total"}, {"BU Name", "Pct of Total"}),
#"Pivoted Column" = Table.Pivot(#"Expanded All Rows1", List.Distinct(#"Expanded All Rows1"[#"BU Name"]), "BU Name", "Pct of Total", List.Sum)
in
#"Pivoted Column"It returns this.
Kudos are appreciated on any posts that helped.
Let me know if this gets you there. If it does't though, I am not sure Power Query will work for you in this case.
Helpful resources
Fabric Community Update - July 2025
Find out what's new and trending in the Fabric community.
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.
