show the data for the last date by category for each subcategory

[Anthony007]

Hello!
It is necessary to transform the table in the query editor in such a way that would group the data by agency, and show the data by category for the maximum date. after build index for each category within agencies. 

@Stachu, can you look at this task? I tried to use m code myself, there is not enough experience.

Need transform the following table:

to table:

 These are my attempts:

let
    Источник = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY9LDoAwCETv0nVjgNJYz9J04Sfe/wiC7ULjmDBh8TLDUGsgnmyEuIQY2LSaNLQI0OabMNtN6YcdvnNnis5JZxlk8oJ9p0nmzsqDycgUxcx/kNFFgC8V7PMuyjdjmmwGS+8fPszvWWS7AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Agency = _t, Category = _t, Аmount = _t]),
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Date", type date}, {"Agency", Int64.Type}, {"Category", type text}, {"Аmount", Int64.Type}}),
    #"Сгруппированные строки" = Table.Group(#"Измененный тип", {"Agency"}, {{"Количество", each Table.AddIndexColumn(Table.Sort(_,{{"Date", Order.Ascending}}), "Index",1,1), type table}}),
    #"Развернутый элемент Количество" = Table.ExpandTableColumn(#"Сгруппированные строки", "Количество", {"Category", "Index"}, {"Category", "Index"})
in
    #"Развернутый элемент Количество"

And I understand that an approximate part of the code should be inserted (like this):

 each List.Max([Date])

Thanks for any help.

[v-piga-msft]

Hi @Anthony007,

Please refer to this query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY9LCsAgDETvkrWCiZHas0gX/dD7H6GZUlBpwCGL50wmrZEkrpEjUyBoNylt4QcOzOSR05RdcmGWTnReI52UKY1Xz3ObZOmkvkS+NFGPoLUMDWTy5Op50EB5uCfZw2+n98iwywK3Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Agency = _t, Category = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Agency", Int64.Type}, {"Category", type text}, {"Amount", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Agency"}, {{"Count", each Table.Group(_, {"Category"}, {{"Count", each Table.LastN(_,1), type table}}), type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Category", "Count"}, {"Category", "Count.1"}),
    #"Expanded Count.1" = Table.ExpandTableColumn(#"Expanded Count", "Count.1", {"Amount", "Index"}, {"Amount", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Count.1",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
    #"Added Index1" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1)
in
    #"Added Index1"

Then you could create an index with the dax formula below. 

Column = RANKX(FILTER('Table1 (2)',EARLIER('Table1 (2)'[Agency])='Table1 (2)'[Agency]),'Table1 (2)'[Index],,ASC)

More details, you could refere to the attachment.

Best  Regards,

Cherry

[Anthony007]

Hi @v-piga-msft, thank for help. But result incorrect.

Agency number 2, category a should have a value of 24. Because there were 2 values ​​in the input data, for 2 dates (01.08.2018-24 and 01.02.2018-38). And if I'm not mistaken, 01.08.2018 later than 01.02.2018.

Also, the index is needed for agencies: