Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anthony007
Helper I
Helper I

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

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:Screenshot_1.png

 

 

to table:Screenshot_2.png

 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.

2 REPLIES 2
v-piga-msft
Resident Rockstar
Resident Rockstar

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

1.png

 

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:

Screenshot_2.png

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.