Forum Discussion

MarkM1's avatar
MarkM1
New Member
1 year ago
Solved

Automatic Custom Columns with YTD Calculation

Good Morning, I am enclosing a screenshot of a Power Query instance I am working on.   The Table shows figures of following fields combinations: CATEGORY (Frames, Lenses) SUPPLIER (Supp...
  • 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"