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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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