Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I'm trying to create a report where we have a line for each employee showing the first date they entered their current grade and the most recent end date (although this might be blank). I want to discount any previous periods in their current grade, where they might have had a temporary promotion in between. I'm only interested in the most latest period they have occupied their current grade.
I can attach an example spreadsheet with the raw data on one tab and the final data, how I'd like it to look, on the other.
Ideally, I'd like to know what steps I have to do to do this in PowerQuery.
I figure it will involve Index columns and grouping by person reference number. My previous attempts at doing this have not worked as I find it hard to distinguish between two different period at the same grade. For example, if I've been in the B2 grade from 01/01/2024 to present and previosuly from 01/01/2023 to 30/06/2023, I want to discount this latter period. But when grouping the data by person reference, both periods are lumped together, which I don't want.
Raw Data
Personal ref | Full name | Grade | Effective date | Effective end date |
3 | Test 3 | B1 | 01/04/2023 | 31/03/2024 |
3 | Test 3 | B1 | 01/11/2022 | 31/03/2023 |
3 | Test 3 | B1 | 01/05/2022 | 31/10/2022 |
3 | Test 3 | B2 | 01/04/2022 | 30/04/2022 |
3 | Test 3 | B2 | 01/04/2020 | 31/03/2022 |
3 | Test 3 | B2 | 01/04/2019 | 31/03/2020 |
3 | Test 3 | B2 | 31/10/2018 | 31/03/2019 |
3 | Test 3 | B2 | 01/04/2018 | 30/10/2018 |
3 | Test 3 | B2 | 01/04/2017 | 31/03/2018 |
3 | Test 3 | B2 | 01/04/2016 | 31/03/2017 |
3 | Test 3 | B2 | 01/04/2015 | 31/03/2016 |
3 | Test 3 | B1 | 01/04/2014 | 31/03/2015 |
3 | Test 3 | B1 | 01/04/2013 | 31/03/2014 |
3 | Test 3 | B1 | 01/08/2010 | 31/03/2013 |
3 | Test 3 | B1 | 01/09/2009 | 31/07/2010 |
Final data
Personal ref | Full name | Grade | Effective date | Effective end date |
3 | Test 3 | B1 | 01/05/2022 | 31/03/2024 |
Solved! Go to Solution.
Hi @mbrierley, check this:
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdJNCsQgDAXgqwyuC33xp9rtnKG74nJOML0/1YrDg0GzEZ98kUBynsaZxRyf7/Wql7eUA7LCrxa2vrgSXA3e5GWgRSqwrN1YI5AWtPCvLXfyaPyCosGdaFp21hjo3qok0qVU+Tu1vnupoiP/reqNddR0YL1NpvNozzpomvdEJnuCVAFPR2Z7sheAPp3YSnO+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Personal ref" = _t, #"Full name" = _t, Grade = _t, #"Effective date" = _t, #"Effective end date" = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Effective date", type date}, {"Effective end date", type date}}, "sk-SK"),
fn_Dates =
(myTable as table)=>
let
// _Detail = GroupedRows{[#"Personal ref"="3"]}[All],
_Detail = myTable,
SortedRows = Table.Sort(_Detail,{{"Effective date", Order.Descending}}),
GroupedRowsInner = Table.Group(SortedRows, {"Grade"}, {{"All Inner", each _, type table}, {"Effective date", each List.Min([Effective date]), type date}, {"Effective end date", each List.Max([Effective end date]), type date}}, GroupKind.Local,
(x,y)=> Value.Compare(x[Grade], y[Grade]) ),
ExpandedDynamic = Table.ExpandTableColumn(GroupedRowsInner, "All Inner", List.RemoveItems(Table.ColumnNames(GroupedRowsInner{0}[All Inner]), Table.ColumnNames(GroupedRowsInner)) ),
KeptFirstRows = Table.FirstN(ExpandedDynamic,1),
ReorderedColumns = Table.ReorderColumns(KeptFirstRows,Table.ColumnNames(_Detail)),
RestoreTypes = Value.ReplaceType(ReorderedColumns, Value.Type(_Detail))
in
RestoreTypes,
GroupedRows = Table.Group(ChangedType, {"Personal ref"}, {{"fn", fn_Dates, type table}}),
ExpandedFn = Table.Combine(GroupedRows[fn])
in
ExpandedFn
Hi @mbrierley, check this:
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdJNCsQgDAXgqwyuC33xp9rtnKG74nJOML0/1YrDg0GzEZ98kUBynsaZxRyf7/Wql7eUA7LCrxa2vrgSXA3e5GWgRSqwrN1YI5AWtPCvLXfyaPyCosGdaFp21hjo3qok0qVU+Tu1vnupoiP/reqNddR0YL1NpvNozzpomvdEJnuCVAFPR2Z7sheAPp3YSnO+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Personal ref" = _t, #"Full name" = _t, Grade = _t, #"Effective date" = _t, #"Effective end date" = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Effective date", type date}, {"Effective end date", type date}}, "sk-SK"),
fn_Dates =
(myTable as table)=>
let
// _Detail = GroupedRows{[#"Personal ref"="3"]}[All],
_Detail = myTable,
SortedRows = Table.Sort(_Detail,{{"Effective date", Order.Descending}}),
GroupedRowsInner = Table.Group(SortedRows, {"Grade"}, {{"All Inner", each _, type table}, {"Effective date", each List.Min([Effective date]), type date}, {"Effective end date", each List.Max([Effective end date]), type date}}, GroupKind.Local,
(x,y)=> Value.Compare(x[Grade], y[Grade]) ),
ExpandedDynamic = Table.ExpandTableColumn(GroupedRowsInner, "All Inner", List.RemoveItems(Table.ColumnNames(GroupedRowsInner{0}[All Inner]), Table.ColumnNames(GroupedRowsInner)) ),
KeptFirstRows = Table.FirstN(ExpandedDynamic,1),
ReorderedColumns = Table.ReorderColumns(KeptFirstRows,Table.ColumnNames(_Detail)),
RestoreTypes = Value.ReplaceType(ReorderedColumns, Value.Type(_Detail))
in
RestoreTypes,
GroupedRows = Table.Group(ChangedType, {"Personal ref"}, {{"fn", fn_Dates, type table}}),
ExpandedFn = Table.Combine(GroupedRows[fn])
in
ExpandedFn
This appears to work 🙂 but I'm struggling to break down what you've done. Would you be able to describe how you're done this?
Hi I created function, but you can see here what is every single step of such function doing:
Function starts below _Detail step
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdJNCsQgDAXgqwyuC33xp9rtnKG74nJOML0/1YrDg0GzEZ98kUBynsaZxRyf7/Wql7eUA7LCrxa2vrgSXA3e5GWgRSqwrN1YI5AWtPCvLXfyaPyCosGdaFp21hjo3qok0qVU+Tu1vnupoiP/reqNddR0YL1NpvNozzpomvdEJnuCVAFPR2Z7sheAPp3YSnO+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Personal ref" = _t, #"Full name" = _t, Grade = _t, #"Effective date" = _t, #"Effective end date" = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Effective date", type date}, {"Effective end date", type date}}, "sk-SK"),
GroupedRows = Table.Group(ChangedType, {"Personal ref"}, {{"All", each _, type table}}),
_Detail = GroupedRows{[#"Personal ref"="3"]}[All],
SortedRows = Table.Sort(_Detail,{{"Effective date", Order.Descending}}),
GroupedRowsInner = Table.Group(SortedRows, {"Grade"}, {{"All Inner", each _, type table}, {"Effective date", each List.Min([Effective date]), type date}, {"Effective end date", each List.Max([Effective end date]), type date}}, GroupKind.Local,
(x,y)=> Value.Compare(x[Grade], y[Grade]) ),
ExpandedDynamic = Table.ExpandTableColumn(GroupedRowsInner, "All Inner", List.RemoveItems(Table.ColumnNames(GroupedRowsInner{0}[All Inner]), Table.ColumnNames(GroupedRowsInner)) ),
KeptFirstRows = Table.FirstN(ExpandedDynamic,1),
ReorderedColumns = Table.ReorderColumns(KeptFirstRows,Table.ColumnNames(_Detail)),
RestoreTypes = Value.ReplaceType(ReorderedColumns, Value.Type(_Detail))
in
RestoreTypes
What you looking for can be achived by using group by whith advanced grouping using first three columns. and Table.column to ungroup them at the end. Curbal's solution has always worked for me with little modification.
<iframe width="560" height="315" src="https://www.youtube.com/embed/QaodJFeX49k?si=ahEvi4Wpa2beaoWW" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>
Video link incase embeded link doest work : https://youtu.be/QaodJFeX49k?si=6RwkXazO4Me2Wx5-
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
12 | |
11 | |
11 | |
8 |
User | Count |
---|---|
43 | |
25 | |
15 | |
14 | |
12 |