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 database in which ID's can have multiple rows with multiple start dates.
I need to know to know if the same ID has the same start dates or different ones.
I already got help on that: https://community.fabric.microsoft.com/t5/Power-Query/Comparing-multiple-rows-with-the-same-ID/m-p/3...
but I need further information about the different start dates, I need to know if they are consecutive or not.
For exmple I can have a table like this:
I need to know that it has the same date (31/12/22), consecutive dates (31/12/22,1/1/23) and non consecutive
In the end I need one row per ID with:
either one column that says one of the following values:
same
consecutive
non consecutive
same and consecutive
same and non consecutive
consecutive and non consecutive
same, consecutive and non consecutive
or three columns:
same | consecutive | non consecutive
with a yes and no values.
Thank you in advance for your help.
Solved! Go to Solution.
Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MlaK1YFwTfXN9I0M4FxjoLSRvpERFoFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, #"Start date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"Start date", type date}},"en-150"),
#"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {
{"same", each if List.Count(List.Distinct([Start date])) < Table.RowCount(_) then "yes" else "no", type text},
{"consecutive", (t)=>
let
sorted = List.Sort(t[Start date]),
offset = List.RemoveFirstN(sorted,1) & {null},
tbl = Table.FromColumns({sorted} & {offset},
type table[d1=date, d2=date]),
com = Table.AddColumn(tbl,"consec", each Date.AddDays([d1],1) = [d2], type logical)
in
if List.AnyTrue(com[consec]) then "yes" else "no", type text
},
{"non consecutive", (t)=>
let
sorted = List.Sort(t[Start date]),
offset = List.RemoveFirstN(sorted,1) & {null},
tbl = Table.FromColumns({sorted} & {offset},
type table[d1=date, d2=date]),
remSame = Table.SelectRows(tbl, each[d1] <> [d2]),
com = Table.AddColumn(remSame,"consec", each Date.AddDays([d1],1) = [d2], type logical)
in
if List.AllTrue(com[consec]) = false then "yes" else "no", type text
}})
in
#"Grouped Rows"
Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MlaK1YFwTfXN9I0M4FxjoLSRvpERFoFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, #"Start date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"Start date", type date}},"en-150"),
#"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {
{"same", each if List.Count(List.Distinct([Start date])) < Table.RowCount(_) then "yes" else "no", type text},
{"consecutive", (t)=>
let
sorted = List.Sort(t[Start date]),
offset = List.RemoveFirstN(sorted,1) & {null},
tbl = Table.FromColumns({sorted} & {offset},
type table[d1=date, d2=date]),
com = Table.AddColumn(tbl,"consec", each Date.AddDays([d1],1) = [d2], type logical)
in
if List.AnyTrue(com[consec]) then "yes" else "no", type text
},
{"non consecutive", (t)=>
let
sorted = List.Sort(t[Start date]),
offset = List.RemoveFirstN(sorted,1) & {null},
tbl = Table.FromColumns({sorted} & {offset},
type table[d1=date, d2=date]),
remSame = Table.SelectRows(tbl, each[d1] <> [d2]),
com = Table.AddColumn(remSame,"consec", each Date.AddDays([d1],1) = [d2], type logical)
in
if List.AllTrue(com[consec]) = false then "yes" else "no", type text
}})
in
#"Grouped Rows"
Thank you so much.
That is what I needed but I'm having problem with the last part.
for the non consecutive I'm getting all yes and I know that's a mistake.
I changed It to:
{"non consecutive", (t)=>
let
sorted = List.Sort(t[Start date]),
offset = List.RemoveFirstN(sorted,1) & {null},
tbl = Table.FromColumns({sorted} & {offset},
type table[d1=date, d2=date]),
com = Table.AddColumn(tbl,"consec", each Duration.Days([d2]-[d1])>1, type logical)
in
if List.AnyTrue(com[consec]) = true then "yes" else "no", type text
}
and now it workes.
Glad you figured it out.
Not having a comprehensive set of examples made it difficult for me.
The problem turns out to be that the last row of the subtable for non-consecutives (or for any of them for that matter) has a null in the offset date column.
This only makes a difference in the algorithm for non-consecutive.
So perhaps just removing that last row would also work.
Something like:
remSame = Table.RemoveLastN(Table.SelectRows(tbl, each[d1] <> [d2]),1),
Thank you. I'll try that.