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.
I've a excel table as shown below.
Now, I want o transform it as this using power query:
For each resource, if leave date is not continous, it should giver same date as start and end date and if it is continous, it should give Min and max dates as start and end dates.
I tried somethng in Power query
Code is:
let
// Load your data into Power Query
YourData = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
// Convert data types if needed
#"Changed Type" = Table.TransformColumnTypes(YourData, {{"Res ID", type text}, {"Leave Date", type date}}),
// Sort the data by Res ID and leave dates
sortedData = Table.Sort(#"Changed Type", {{"Res ID", Order.Ascending}, {"Leave Date", Order.Ascending}}),
// Add an index column
addIndex = Table.AddIndexColumn(sortedData, "Index", 0, 1, Int64.Type),
// Add a custom column to identify the start of a new continuous leave period for each resource
addStartFlag = Table.AddColumn(addIndex, "Start Flag", each if [Index] = 0 or [Res ID] <> Table.Column(addIndex, "Res ID"){[Index] - 1} then 1 else 0, type logical),
// Add a custom column to identify the continuous leave period index for each resource
addPeriodIndex = Table.AddColumn(addStartFlag, "Period Index", each List.PositionOf(List.Select(addStartFlag[Start Flag], each _ = 1), 1), Int64.Type),
// Group the data by Res ID and the continuous leave period index
groupedData = Table.Group(addPeriodIndex, {"Res ID", "Period Index"}, {{"Start Date", each List.Min([Leave Date]), type date}, {"End Date", each List.Max([Leave Date]), type date}}),
// Remove the continuous leave period index column
#"Removed Columns" = Table.RemoveColumns(groupedData, {"Period Index"})
in
#"Removed Columns"
But it is giving unique Res ID and Min and Max dates as below.
Can anyone help where I'm going wrong to get the desired result as said above?
Solved! Go to Solution.
Hi @SR_dataguy
use Table.Group with GrouKind.Local
let
// Load your data into Power Query
YourData = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
// Convert data types if needed
#"Changed Type" = Table.TransformColumnTypes(YourData, {{"Res ID", type text}, {"Leave Date", type date}}),
// Sort the data by Res ID and leave dates
sortedData = Table.Sort(#"Changed Type", {{"Res ID", Order.Ascending}, {"Leave Date", Order.Ascending}}),
// Add an index column
addIndex = Table.AddIndexColumn(sortedData, "Index", 0, 1, Int64.Type),
Group = Table.Group(
addIndex,
{"Res ID", "Leave Date", "Index"},
{{"Start_Date", each List.Min(_[Leave Date]), type date},
{"End_Date", each List.Max(_[Leave Date]), type date}},
GroupKind.Local,
(x,y) => Byte.From( (y[Index]-x[Index]) <> Duration.Days(y[Leave Date]-x[Leave Date]))
)
in Group
Stéphane
Hi @SR_dataguy
use Table.Group with GrouKind.Local
let
// Load your data into Power Query
YourData = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
// Convert data types if needed
#"Changed Type" = Table.TransformColumnTypes(YourData, {{"Res ID", type text}, {"Leave Date", type date}}),
// Sort the data by Res ID and leave dates
sortedData = Table.Sort(#"Changed Type", {{"Res ID", Order.Ascending}, {"Leave Date", Order.Ascending}}),
// Add an index column
addIndex = Table.AddIndexColumn(sortedData, "Index", 0, 1, Int64.Type),
Group = Table.Group(
addIndex,
{"Res ID", "Leave Date", "Index"},
{{"Start_Date", each List.Min(_[Leave Date]), type date},
{"End_Date", each List.Max(_[Leave Date]), type date}},
GroupKind.Local,
(x,y) => Byte.From( (y[Index]-x[Index]) <> Duration.Days(y[Leave Date]-x[Leave Date]))
)
in Group
Stéphane
Thank you. It worked.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.