Forum Discussion

MarkM1's avatar
MarkM1
New Member
2 years ago
Solved

Automatic Custom Columns with YTD Calculation

Good Morning,
I am enclosing a screenshot of a Power Query instance I am working on.
 
Query
The Table shows figures of following fields combinations:
  • CATEGORY (Frames, Lenses)
  • SUPPLIER (Supplier 1, Supplier 2)
  • CLASS (A, B)
  • AGGREGATE (Sell-Out, Sell-In)
  • MONTHS (from January to June)
The Table derives from a Query and the database is supposed to be updated each month.
What I am trying to achieve, but I am unable to, is to create a custom column with the YTD calculation for each single month.
In this example, being the months 6 (From Jan to Jun) there should be 6 additional columns as follows:
  • YTD January (equals to January naturally)
  • YTD February
  • YTD March
  • ... and so on
calculating YTD for each combination Category/Supplier/Class/Aggregate, and naturally once the query is refreshed after updating the database, columns shall upgrade too considering the number of months inside the database.
 
Can you please help me?
 
Thank you
 
Mark
Need Help
  • jgeddes's avatar
    jgeddes
    1 year ago

    There is no way for you to attach a file directly. Generally a shared cloud storage location that is accessable to a public user would be the only way to share a file. 
    Specific to the code, it may be an issue with date formating. 

    Try this version to see if it removes the errors...

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc5RDoAgCADQu/DtJiIid3F+1P0PEdkqavoBg/EGtAYbBEiULY/KAiliioTE10gZepjBVDwswjLgbh0X8TA7SOds7qg6J7ebHDZHr1sy9axqfc7+HH3XsS4gZgd1/NcP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CATEGORY = _t, SUPPLIER = _t, CLASS = _t, AGGREGATE = _t, MONTH = _t, VALUE = _t]),
    #"Parsed Date" = 
    Table.TransformColumns(
        Source,
        {
            {"MONTH", each Date.From(DateTimeZone.From(_, "de-DE")), type date}
        }
    ),
    #"Changed Type" = 
    Table.TransformColumnTypes(
        #"Parsed Date",
        {
            {"CATEGORY", type text}, 
            {"SUPPLIER", Int64.Type}, 
            {"CLASS", type text}, 
            {"AGGREGATE", Int64.Type}, 
            {"MONTH", type date}, 
            {"VALUE", Int64.Type}
        }
    ),
    #"Grouped Rows" = 
    Table.Group(
        #"Changed Type", 
        {"CATEGORY", "SUPPLIER", "CLASS", "AGGREGATE"}, 
        {
            {"January_YTD", each List.Sum(Table.SelectRows(_, each Date.Month([MONTH]) <= 1)[VALUE]), type nullable number},
            {"February_YTD", each List.Sum(Table.SelectRows(_, each Date.Month([MONTH]) <= 2)[VALUE]), type nullable number},
            {"allRows", each _, type table [CATEGORY=nullable text, SUPPPLIER=nullable number, CLASS=nullable text, AGGREGATE=nullable number, MONTH=nullable date, VALUE=nullable number]}
        }
    ),
    #"Expanded allRows" = 
    Table.ExpandTableColumn(
        #"Grouped Rows", 
        "allRows", 
        {"MONTH", "VALUE"}, 
        {"MONTH", "VALUE"}
    )
in
    #"Expanded allRows"



3 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    2 years ago

    Here is an example of one possible approach to this. 
    I have created a small sample set that mimics your data. Please copy this code into the advanced editor of a blank query so you can review each step.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc9dDoAgCADgu/DsJhAid3E+1P0PkdlkrrIHmYxv/JQCOwQg3lrsv/aQInJkZLlLJlDDAlJymFS0w6NlkvQBtwH5qi0d5+F0uI/BHGlyf8ycZcs+9u14aie2huh3WN+vng==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CATEGORY = _t, SUPPLIER = _t, CLASS = _t, AGGREGATE = _t, MONTH = _t, VALUE = _t]),
    #"Changed Type" = 
    Table.TransformColumnTypes(
        Source,
        {
            {"CATEGORY", type text}, 
            {"SUPPLIER", Int64.Type}, 
            {"CLASS", type text}, 
            {"AGGREGATE", Int64.Type}, 
            {"MONTH", type date}, 
            {"VALUE", Int64.Type}
        }
    ),
    #"Grouped Rows" = 
    Table.Group(
        #"Changed Type", 
        {"CATEGORY", "SUPPLIER", "CLASS", "AGGREGATE"}, 
        {
            {"January_YTD", each List.Sum(Table.SelectRows(_, each Date.Month([MONTH]) <= 1)[VALUE]), type nullable number},
            {"February_YTD", each List.Sum(Table.SelectRows(_, each Date.Month([MONTH]) <= 2)[VALUE]), type nullable number},
            {"allRows", each _, type table [CATEGORY=nullable text, SUPPPLIER=nullable number, CLASS=nullable text, AGGREGATE=nullable number, MONTH=nullable date, VALUE=nullable number]}
        }
    ),
    #"Expanded allRows" = 
    Table.ExpandTableColumn(
        #"Grouped Rows", 
        "allRows", 
        {"MONTH", "VALUE"}, 
        {"MONTH", "VALUE"}
    )
in
    #"Expanded allRows"
    • MarkM1's avatar
      MarkM1
      New Member
      2 years ago

      Hi Jgeddes,

      first of all thanks for your help. I run the code but it seems it does not work properly. Here follows a snapshot from Power Query Editor:

       

       

      Is there any way to upload a file here in the forum? This way I could provide you with a structured example.

       

      Mark

       

      • jgeddes's avatar
        jgeddes
        Super User
        1 year ago

        There is no way for you to attach a file directly. Generally a shared cloud storage location that is accessable to a public user would be the only way to share a file. 
        Specific to the code, it may be an issue with date formating. 

        Try this version to see if it removes the errors...

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc5RDoAgCADQu/DtJiIid3F+1P0PEdkqavoBg/EGtAYbBEiULY/KAiliioTE10gZepjBVDwswjLgbh0X8TA7SOds7qg6J7ebHDZHr1sy9axqfc7+HH3XsS4gZgd1/NcP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CATEGORY = _t, SUPPLIER = _t, CLASS = _t, AGGREGATE = _t, MONTH = _t, VALUE = _t]),
    #"Parsed Date" = 
    Table.TransformColumns(
        Source,
        {
            {"MONTH", each Date.From(DateTimeZone.From(_, "de-DE")), type date}
        }
    ),
    #"Changed Type" = 
    Table.TransformColumnTypes(
        #"Parsed Date",
        {
            {"CATEGORY", type text}, 
            {"SUPPLIER", Int64.Type}, 
            {"CLASS", type text}, 
            {"AGGREGATE", Int64.Type}, 
            {"MONTH", type date}, 
            {"VALUE", Int64.Type}
        }
    ),
    #"Grouped Rows" = 
    Table.Group(
        #"Changed Type", 
        {"CATEGORY", "SUPPLIER", "CLASS", "AGGREGATE"}, 
        {
            {"January_YTD", each List.Sum(Table.SelectRows(_, each Date.Month([MONTH]) <= 1)[VALUE]), type nullable number},
            {"February_YTD", each List.Sum(Table.SelectRows(_, each Date.Month([MONTH]) <= 2)[VALUE]), type nullable number},
            {"allRows", each _, type table [CATEGORY=nullable text, SUPPPLIER=nullable number, CLASS=nullable text, AGGREGATE=nullable number, MONTH=nullable date, VALUE=nullable number]}
        }
    ),
    #"Expanded allRows" = 
    Table.ExpandTableColumn(
        #"Grouped Rows", 
        "allRows", 
        {"MONTH", "VALUE"}, 
        {"MONTH", "VALUE"}
    )
in
    #"Expanded allRows"