Output only the latest value for X and the previous value

Anonymous — Mon, 05 Aug 2019 09:31:40 GMT

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

Re: Output only the latest value for X and the previous value

Mariusz — Mon, 05 Aug 2019 10:24:41 GMT

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.

topic Re: Output only the latest value for X and the previous value in DAX Commands and Tips

Output only the latest value for X and the previous value

Re: Output only the latest value for X and the previous value