Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hi to everybody!
I have a table with position codes who can appear more than once as in one period can be many short term contracts.
Position code | Start Date | End Date |
a_001 | 01/01/2022 | 30/05/2022 |
a_002 | 01/01/2022 | 30/01/2022 |
a_002 | 01/02/2022 | 28/02/2022 |
a_002 | 01/03/2022 | 30/04/2022 |
b_001 | 01/06/2022 | 30/08/2022 |
b_002 | 01/08/20220 | 30/03/2022 |
b_003 | 01/05/2022 | 30/06/2022 |
b_003 | 02/07/2022 | 30/07/2022 |
I want to create in power query: a) a column e.g. duplicates based on which I can see the duplicate values, and b) for the duplicate ones to have a colum index with values for earliest to latest start date. This is how I imagine it:
Position code | Start Date | End Date | Duplicate | Duplicate index |
a_001 | 01/01/2022 | 30/05/2022 | no | (1 or blank, haven't decided) |
a_002 | 01/01/2022 | 30/01/2022 | yes | 1 |
a_002 | 01/03/2022 | 30/04/2022 | yes | 2 |
a_002 | 01/02/2022 | 28/02/2022 | yes | 3 |
b_001 | 01/06/2022 | 30/08/2022 | no | (1 or blank, haven't decided) |
b_002 | 01/08/20220 | 30/03/2022 | no | 1(1 or blank, haven't decided) |
b_003 | 01/05/2022 | 30/06/2022 | yes | 1 |
b_003 | 02/07/2022 | 30/07/2022 | yes | 2 |
Any ideas?
@KatBous Paste this code in the advanced editor:
let
Source = Table.FromRows (
Json.Document (
Binary.Decompress (
Binary.FromText (
"bc7BCcAgEETRXjwLjrPR2ItIMP0XEUFN1iDs5cNj2JxNvQBvrIF37QiyhcAh9Ci2G+6M3xtOw/TG34jeOT5zq3+iNmk1cydpI6uRYYI2cWPocGozopQH",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
in
type table [ #"Position code" = _t, #"Start Date" = _t, #"End Date" = _t ]
),
ChangedType =
Table.TransformColumnTypes (
Source,
{ { "Position code", type text }, { "Start Date", type date }, { "End Date", type date } },
"en-GB"
),
GroupedRows =
Table.Group (
ChangedType,
{ "Position code" },
{
{
"Transformation",
( Group ) =>
Table.AddIndexColumn (
Table.AddColumn (
Group,
"Duplicate",
each if Table.RowCount ( Group ) > 1 then "yes" else "no"
),
"Duplicate Index",
1,
1
),
type table [
Start Date = date,
End Date = date,
Duplicate = text,
Duplicate Index = Int64.Type
]
}
}
),
ExpandedTransformation =
Table.ExpandTableColumn (
GroupedRows,
"Transformation",
{ "Start Date", "End Date", "Duplicate", "Duplicate Index" },
{ "Start Date", "End Date", "Duplicate", "Duplicate Index" }
)
in
ExpandedTransformation
Thank you @AntrikshSharma. This is exactly what I need.
But, since my query has already some steps in the editor, could you help me on how to add the above commands?
Or what I should replace in the above commands if I reference the existing query and apply in the new one the above commands?