Forum Discussion

Pawel_C's avatar
Pawel_C
Frequent Visitor
3 years ago
Solved

Select two latest values per attribute

Hi I'm really rookie in terms of using DAX and PowerQuery, but need to solve a problem.  Having a table with data for each attribute in a daily manner: (note! not all attributes are reported eve...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    3 years ago

    Insert this step where #"Changed Type" should be replaced with your previous step

     

    = Table.Combine(Table.Group(#"Changed Type", {"Category"}, {{"All", each Table.FromColumns(Table.ToColumns(Table.MaxN(_, "DATE", 2)) & {{"Latest available date", "2nd latest available date"}}, Table.ColumnNames(_)&{"Date Text"})}})[All])

     

    Complete code in action

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA30TcyMDJW0lFySk1NA1KGQE6sDkjGGCYTnJGaWgCkTUzN0KWgmswtLKEyRuiagOaZQOUM0XQBZUzRpZDsMleKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, Category = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"Category", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Combine(Table.Group(#"Changed Type", {"Category"}, {{"All", each Table.FromColumns(Table.ToColumns(Table.MaxN(_, "DATE", 2)) & {{"Latest available date", "2nd latest available date"}}, Table.ColumnNames(_)&{"Date Text"})}})[All])
in
    #"Grouped Rows"

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
3 years ago

I will need the sample data and output the way you gave in problem.

Pawel_C's avatar
Pawel_C
Frequent Visitor
3 years ago

I need a selection of rows like in the example below (no need to pivot, I can do that myself).

desired output

Here's my sample data

sample input