Output only the latest value for X and the previous value

[Anonymous]

Hi,

I have a table that looks something like this

Seller || Product ID || Price || DateOffered

A                 2              1          20190101

Z                 3              7          20190102

A                 1              7          20190103

B                 1              2          20190104

B                 2              3          20190105

C                 1              5          20190106

A                 1              6          20190107

I only want to get the data where seller A is the current seller,

whos is the previous seller and what is the previous price.

output:

Product  || Current Seller ||  Current Price || Previous Seller ||  Previous Price ||

    1                    A                       6                         C                            5

[Mariusz]

Hi @Anonymous 

Please see the below M code, this should give you the desired result. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcjy0QAGMlHSUjIDYEEQbGFoaGBoYKsXqRCtFISkwBmJzhAIjsAJkEwxRFRiDFTihKTBCKDDBUGAEtQaqwBSswBnNBFOEAjOsbjBDKDBXio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Seller = _t, #"Product ID" = _t, Price = _t, DateOffered = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Seller", type text}, {"Product ID", Int64.Type}, {"Price", Int64.Type}, {"DateOffered", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product ID"}, {{"Count", 
        each
            Table.AddIndexColumn( 
                Table.FirstN( 
                    Table.SelectColumns( 
                        Table.Sort( _, {{"DateOffered", Order.Descending}} ), {"Seller", "Price"} ), 2 
                ),
                "Index", 0, 1
            ), 
        type table [Seller=text, Price=number, Index=text]}}
    ),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Seller", "Price", "Index"}, {"Seller", "Price", "Index"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Count", {"Product ID", "Index"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Index", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","0","Current",Replacer.ReplaceText,{"Index"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","1","Previous",Replacer.ReplaceText,{"Index"}),
    #"Merged Columns" = Table.CombineColumns(#"Replaced Value1",{"Index", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Pivoted Column",{{"Product ID", Int64.Type}, {"Current Seller", type text}, {"Current Price", Int64.Type}, {"Previous Seller", type text}, {"Previous Price", Int64.Type}})
in
    #"Changed Type2"

Input table.

Output table.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.