Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello, I have a table with the following informations, the column unique_id is just the merge of scenario & year:
Period | Amount | scenario | year | unique_id |
202001 | 100 | FCT1 | 2020 | FCT12020 |
202101 | 150 | FCT1 | 2021 | FCT12021 |
202002 | 300 | FCT1 | 2020 | FCT12020 |
202103 | 400 | FCT1 | 2021 | FCT12021 |
202001 | 250 | FCT2 | 2020 | FCT22020 |
202101 | 120 | FCT2 | 2021 | FCT22021 |
202002 | 485 | FCT2 | 2020 | FCT22020 |
202103 | 695 | FCT2 | 2021 | FCT22021 |
I would like to identify the rows for each unique_id where the biggest period.
Period | Amount | scenario | year | unique_id | Check |
202001 | 100 | FCT1 | 2020 | FCT12020 | FALSE |
202101 | 150 | FCT1 | 2021 | FCT12021 | FALSE |
202002 | 300 | FCT1 | 2020 | FCT12020 | TRUE |
202103 | 400 | FCT1 | 2021 | FCT12021 | TRUE |
202001 | 250 | FCT2 | 2020 | FCT22020 | FALSE |
202101 | 120 | FCT2 | 2021 | FCT22021 | FALSE |
202002 | 485 | FCT2 | 2020 | FCT22020 | TRUE |
202103 | 695 | FCT2 | 2021 | FCT22021 | TRUE |
To end up with the following table
Period | Amount | scenario | year | unique_id |
202002 | 300 | FCT1 | 2020 | FCT12020 |
202103 | 400 | FCT1 | 2021 | FCT12021 |
202002 | 485 | FCT2 | 2020 | FCT22020 |
202103 | 695 | FCT2 | 2021 | FCT22021 |
Thanks!
Solved! Go to Solution.
Paste the code below into a blank query.
Read the comments and explore the Applied Steps to understand the algorithm.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjAwVNJRMjQwAJJuziEgDkgUygMzY3XAKg0hKk1RVRoiVBrCVBoYGAFFjYky0xgoamJAjJlgSZjtRihmGmFxpxGqSkOESjR3mliYEmEmyJ1mlqZ4zIwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Amount = _t, scenario = _t, year = _t, unique_id = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", Int64.Type}, {"Amount", Int64.Type}, {"scenario", type text}, {"year", Int64.Type}, {"unique_id", type text}}),
//group by unique id
// then Select the row with the maximum Amount
#"Grouped Rows" = Table.Group(#"Changed Type", {"unique_id"}, {
{"Amount", (t)=>Table.SelectRows(t, each [Amount]=List.Max(t[Amount])),
type table[Period=Int64.Type, Amount=number, scenario=text,year=Int64.Type]}
}),
//Expand the subtables
#"Expanded Amount" = Table.ExpandTableColumn(#"Grouped Rows", "Amount",
{"Period", "Amount", "scenario", "year"},
{"Period", "Amount", "scenario", "year"}),
//Return columns to original order
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Amount",Table.ColumnNames(Source))
in
#"Reordered Columns"
Data
Results
Paste the code below into a blank query.
Read the comments and explore the Applied Steps to understand the algorithm.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjAwVNJRMjQwAJJuziEgDkgUygMzY3XAKg0hKk1RVRoiVBrCVBoYGAFFjYky0xgoamJAjJlgSZjtRihmGmFxpxGqSkOESjR3mliYEmEmyJ1mlqZ4zIwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Amount = _t, scenario = _t, year = _t, unique_id = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", Int64.Type}, {"Amount", Int64.Type}, {"scenario", type text}, {"year", Int64.Type}, {"unique_id", type text}}),
//group by unique id
// then Select the row with the maximum Amount
#"Grouped Rows" = Table.Group(#"Changed Type", {"unique_id"}, {
{"Amount", (t)=>Table.SelectRows(t, each [Amount]=List.Max(t[Amount])),
type table[Period=Int64.Type, Amount=number, scenario=text,year=Int64.Type]}
}),
//Expand the subtables
#"Expanded Amount" = Table.ExpandTableColumn(#"Grouped Rows", "Amount",
{"Period", "Amount", "scenario", "year"},
{"Period", "Amount", "scenario", "year"}),
//Return columns to original order
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Amount",Table.ColumnNames(Source))
in
#"Reordered Columns"
Data
Results
THanks!