none
Custom column to mark the last entry RRS feed

  • Question

  • Hi,

    I am wondering whether it is possible to have a custom column in Power Query (let's say of true/false type) that would show true for every row that holds the last entry for a given month and product.

    Let's assume we have the following data set.

    ID|Product|Date|LastEntry?(Our custom column)

    1|Tea|2018-01-02|F

    2|Tea|2018-01-30|T

    3|Tea|2018-02-06|T

    4|Coffee|2018-01-09|F

    5|Tea|2018-03-02|T

    6|Tea|2018-03-01|F

    7|Coffee|2018-01-10|T

    8|Tea|2018-03-02|T

    Can this be done in PQ or is it better to do this in DAX?

    Kind regards,


    -- Igor M.


    • Edited by Igor-M Thursday, September 13, 2018 8:28 PM
    Thursday, September 13, 2018 8:27 PM

Answers

  • It is entirely possible to perform this with Power Query,  but it might become a pretty "expensive" query if your table has millions of rows even if we add some sort of surrogate key design (for the products) to make the query more efficient.

    Creating a Calc Column in DAX with the EARLIER function is probably a more optimal way, unless you need that flag for other type of ETL transformation and finding that last record for a given month is just step 1 of a series of ETL workflows - in that case you'll need to make that happen in Power Query no matter what.

    Also, you need to define how to handle ties like the row 5 and row 8. Should both be TRUE or only one?

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMqwJSU2sMTIwtNA1MNQ1MKpxU4rViVYyQhE2NqgJAQsbIwkb6RqYQYVNapzz09JSUxHmWELNMUXSYAwyHqLBDFXYEKraHN0cQ5jFFljMiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ID|Product|Date|LastEntry?(Our custom column)" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID|Product|Date|LastEntry?(Our custom column)", type text}}),
        #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
        #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type2", [PromoteAllScalars=true]),
        OriginalTable = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Product", type text}, {"Date", type date}, {"LastEntry?(Our custom column)", type text}}),
        #"Sorted Rows" = Table.Sort(OriginalTable,{{"Date", Order.Ascending}}),
        #"Inserted Month" = Table.AddColumn(#"Sorted Rows", "Month", each Date.Month([Date]), Int64.Type),
        #"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([Date]), Int64.Type),
        #"Grouped Rows" = Table.Group(#"Inserted Year", {"Year", "Month", "Product"}, {{"Rows", each _, type table}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "LastRow", each  Table.Sort(Table.LastN([Rows], 1),{{"Date", Order.Ascending}}) ),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows", "Year", "Month", "Product"}),
        #"Expanded LastRow" = Table.ExpandTableColumn(#"Removed Columns", "LastRow", {"ID"}, {"LastRow.ID"}),
        #"Changed Type4" = Table.TransformColumnTypes(#"Expanded LastRow",{{"LastRow.ID", Int64.Type}}),
        #"Merged Queries" = Table.NestedJoin(OriginalTable,{"ID"},#"Changed Type4",{"LastRow.ID"},"Flag",JoinKind.LeftOuter),
        #"Aggregated Flag" = Table.AggregateTableColumn(#"Merged Queries", "Flag", {{"LastRow.ID", List.NonNullCount, "Count (Not Blank) of LastRow.ID"}}),
        #"Changed Type3" = Table.TransformColumnTypes(#"Aggregated Flag",{{"Count (Not Blank) of LastRow.ID", type logical}})
    in
        #"Changed Type3"

    Here's a sample on how to make it happen in Power Query where I handle the ties by simply choosing the last record (defined by their ID number).

    Thursday, September 13, 2018 11:12 PM
  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Ditto Miguel.
    With PP and PQ.
    With LastDay, not T/F.
    No ties.
    http://www.mediafire.com/file/hjqqaihstouo3pt/09_13_18.xlsx/file
    http://www.mediafire.com/file/84nve6f5oqjcqme/09_13_18.pdf/file

    Friday, September 14, 2018 1:49 AM

All replies

  • It is entirely possible to perform this with Power Query,  but it might become a pretty "expensive" query if your table has millions of rows even if we add some sort of surrogate key design (for the products) to make the query more efficient.

    Creating a Calc Column in DAX with the EARLIER function is probably a more optimal way, unless you need that flag for other type of ETL transformation and finding that last record for a given month is just step 1 of a series of ETL workflows - in that case you'll need to make that happen in Power Query no matter what.

    Also, you need to define how to handle ties like the row 5 and row 8. Should both be TRUE or only one?

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMqwJSU2sMTIwtNA1MNQ1MKpxU4rViVYyQhE2NqgJAQsbIwkb6RqYQYVNapzz09JSUxHmWELNMUXSYAwyHqLBDFXYEKraHN0cQ5jFFljMiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ID|Product|Date|LastEntry?(Our custom column)" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID|Product|Date|LastEntry?(Our custom column)", type text}}),
        #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
        #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type2", [PromoteAllScalars=true]),
        OriginalTable = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Product", type text}, {"Date", type date}, {"LastEntry?(Our custom column)", type text}}),
        #"Sorted Rows" = Table.Sort(OriginalTable,{{"Date", Order.Ascending}}),
        #"Inserted Month" = Table.AddColumn(#"Sorted Rows", "Month", each Date.Month([Date]), Int64.Type),
        #"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([Date]), Int64.Type),
        #"Grouped Rows" = Table.Group(#"Inserted Year", {"Year", "Month", "Product"}, {{"Rows", each _, type table}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "LastRow", each  Table.Sort(Table.LastN([Rows], 1),{{"Date", Order.Ascending}}) ),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows", "Year", "Month", "Product"}),
        #"Expanded LastRow" = Table.ExpandTableColumn(#"Removed Columns", "LastRow", {"ID"}, {"LastRow.ID"}),
        #"Changed Type4" = Table.TransformColumnTypes(#"Expanded LastRow",{{"LastRow.ID", Int64.Type}}),
        #"Merged Queries" = Table.NestedJoin(OriginalTable,{"ID"},#"Changed Type4",{"LastRow.ID"},"Flag",JoinKind.LeftOuter),
        #"Aggregated Flag" = Table.AggregateTableColumn(#"Merged Queries", "Flag", {{"LastRow.ID", List.NonNullCount, "Count (Not Blank) of LastRow.ID"}}),
        #"Changed Type3" = Table.TransformColumnTypes(#"Aggregated Flag",{{"Count (Not Blank) of LastRow.ID", type logical}})
    in
        #"Changed Type3"

    Here's a sample on how to make it happen in Power Query where I handle the ties by simply choosing the last record (defined by their ID number).

    Thursday, September 13, 2018 11:12 PM
  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Ditto Miguel.
    With PP and PQ.
    With LastDay, not T/F.
    No ties.
    http://www.mediafire.com/file/hjqqaihstouo3pt/09_13_18.xlsx/file
    http://www.mediafire.com/file/84nve6f5oqjcqme/09_13_18.pdf/file

    Friday, September 14, 2018 1:49 AM