complex power query transformation help

[rob_vander2]

Hi All,

I have below dataset and required output as attached. I need to rolling quarterly  figure for Value for each combination of ID,Category,Sub Category and Type. For example, for Month Dec-24, combination of ID,Category,Sub Category and Type, I need to go back Oct-24,Nov-24 & Dec-24 and aggregate the Value.

ID	Category	Sub Category	Type	Month	Value
1	AA	A	T1	Sep-24	100
1	AA	A	T1	Oct-24	50
1	AA	B	T2	Nov-24	100
1	AA	B	T2	Dec-24	90
1	AA	A	T1	Sep-24	100
1	AA	A	T1	Oct-24	50
2	AA	B	T2	Nov-24	100
2	AA	B	T2	Dec-24	90
1	AA	A	T1	Sep-24	100
1	AB	B	T2	Oct-24	50
2	AA	A	T1	Nov-24	100
2	AA	A	T1	Dec-24	90

[Akash_Varuna · Accepted solution]

Hi @rob_vander2 Could you try this please  After Loading the data to Power Query

• Sort the Data:

  • Sort the table by ID, Category, Sub Category, Type, and Month in ascending order.

• Create a Custom Column for Rolling Total:

  • Go to Add Column > Custom Column:
    List.Sum(
    Table.SelectRows(
    #"Previous Step",
    (x) =>
    x[ID] = [ID] and
    x[Category] = [Category] and
    x[Sub Category] = [Sub Category] and
    x[Type] = [Type] and
    Date.FromText(x[Month]) <= Date.FromText([Month]) and
    Date.FromText(x[Month]) >= Date.AddMonths(Date.FromText([Month]), -2)
    )[Value]
    )

    Rename the custom column

    If this post helped please do give a kudos and accept this as a solution

    Thanks In Advance 

[dufoq3]

Hi @rob_vander2, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0BBFAHALiBacW6BqZABmGBgZKsTrYlPgnl0CUmKKpcAKpMAISfvllOAyBK3FJTYYosYSoUAAyMTF2B5DhRiPCbsRUQn03OiEbj9ONcENwuxGuBMWNsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Category = _t, #"Sub Category" = _t, Type = _t, Month = _t, Value = _t]),
    // You can delete this step when applying on real data.
    ReplacedBlanks = Table.TransformColumns(Source, {}, each if Text.Trim(_) = "" then null else _),
    ChangedType = Table.TransformColumnTypes(ReplacedBlanks,{{"Value", Int64.Type}}),
    TransformedValue = Table.Combine(Table.Group(ChangedType, "ID", {{"T", each Table.Combine(Table.Group(_, {"ID", "Category", "Sub Category", "Type"}, {{"T2", (x)=> if x{0}[ID] = null then x else let a = Table.ToRecords(x) in Table.FromRecords(List.RemoveLastN(a, 1) & {List.Last(a) & [Value = List.Sum(x[Value])]}) , type table}})[T2]), type table}}, 0, (x,y)=> Byte.From(y is null))[T])
in
    TransformedValue

[rob_vander2]

@dufoq3  Could you please explain how TransformedValue step works?

[dufoq3]

TransformedValue step explanation in steps:

1. Group rows by "ID"
2. Inner group rows by columns: "ID", "Category", "Sub Category", "Type" 
3. Replace last [Value] with sum of [Value] column (for this inner group step)
4. Combine Inner group to 1 table
5. Combine Outer group to 1 table

[rob_vander2]

@dufoq3  How performant it will be? I have millions of data.

[dufoq3 · Accepted solution]

I don't know, you have to test it 😉

You can also try this one (I've just added one 0 as 4th argument of inner Table.Group), but you can notice the speed difference. This will work if your data is sturcutred as your sample.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0BBFAHALiBacW6BqZABmGBgZKsTrYlPgnl0CUmKKpcAKpMAISfvllOAyBK3FJTYYosYSoUAAyMTF2B5DhRiPCbsRUQn03OiEbj9ONcENwuxGuBMWNsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Category = _t, #"Sub Category" = _t, Type = _t, Month = _t, Value = _t]),
    // You can delete this step when applying on real data.
    ReplacedBlanks = Table.TransformColumns(Source, {}, each if Text.Trim(_) = "" then null else _),
    ChangedType = Table.TransformColumnTypes(ReplacedBlanks,{{"Value", Int64.Type}}),
    TransformedValue = Table.Combine(Table.Group(ChangedType, "ID", {{"T", each Table.Combine(Table.Group(_, {"ID", "Category", "Sub Category", "Type"}, {{"T2", (x)=> if x{0}[ID] = null then x else let a = Table.ToRecords(x) in Table.FromRecords(List.RemoveLastN(a, 1) & {List.Last(a) & [Value = List.Sum(x[Value])]}) , type table}}, 0)[T2]), type table}}, 0, (x,y)=> Byte.From(y is null))[T])
in
    TransformedValue

[Akash_Varuna · Accepted solution]

Hi @rob_vander2 Could you try this please  After Loading the data to Power Query

• Sort the Data:

  • Sort the table by ID, Category, Sub Category, Type, and Month in ascending order.

• Create a Custom Column for Rolling Total:

  • Go to Add Column > Custom Column:
    List.Sum(
    Table.SelectRows(
    #"Previous Step",
    (x) =>
    x[ID] = [ID] and
    x[Category] = [Category] and
    x[Sub Category] = [Sub Category] and
    x[Type] = [Type] and
    Date.FromText(x[Month]) <= Date.FromText([Month]) and
    Date.FromText(x[Month]) >= Date.AddMonths(Date.FromText([Month]), -2)
    )[Value]
    )

    Rename the custom column

    If this post helped please do give a kudos and accept this as a solution

    Thanks In Advance 

[rob_vander2]

@Akash_Varuna  I am not sure how to apply this. Could you share PBIX file?