March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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.
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
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:
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
98 | |
86 | |
69 | |
61 |
User | Count |
---|---|
140 | |
121 | |
108 | |
99 | |
96 |