Identifying dates overlapping - Power Query

[Susanne72]

Hello,

Hoping that someone can help me bumping in to Power Query formulas...

In short, I have a data set which I have cleaned in Power Query. Now I need to identify overlapping date ranges.

Column G contains uniq IDs. I must identify lines containing same Uniq ID AND wheter or not their date range are overlapping (column H/I). Preferably a power query formula testing and returning yes/no in column W. If a Uniq ID has no doubles the returning value should be "no doubles".
I have tried to search for a solution and I have tried several suggestions, but it seems all the "do so"s are matching more than one column and I get lost in the coding.

Below is an overview of all my columns.

A	Source.Name
B	Account code
C	Account selection
D	Item relation
E	Quantity
F	Unit
G	Uniq ID
H	From date
I	To date
J	Amount currency
K	Currency
L	Comment
M	Created by
N	Created date
O	Find next
P	Cost center
Q	ABC
R	Article name default language
S	Vendor
T	Name
U	Modified by
V	Modified date
W	Overlap in date range (Y/N?)

BR
Susanne

 

[Monty]

Try this

let
    // Assuming your table is named "Source"
    Source = YourDataSource,
    // Step 1: Group by Unique ID
    Grouped = Table.Group(Source, {"Uniq ID"}, {
        // Step 2: Check for overlapping date ranges within each group
        {"Overlap", each List.Count(List.Select(
            Table.ToRecords(_),
            each [#"From date"] <= _[#"To date"] and [#"To date"] >= _[#"From date"]
            )) > 1, type logical}
        }),
    // Step 3: Add a custom column indicating overlaps or no overlaps
    AddedCustomColumn = Table.AddColumn(Grouped, "Overlap in date range (Y/N?)", each
        if [Overlap] then "Yes" else "No doubles")
in
    AddedCustomColumn

 

[Susanne72]

Hey Monty
Is this the formula to be pasted in the "Custom column formula"? It doesn't seem to accept much of it. Maybe a misunderstanding? My file name (=tablename I guess?) is "Datafiler komplet". In Excel I use semicolon as seperator does that match your code?
BR Susanne

 

[Monty]

Here's a step-by-step guide:

1. Open your Power Query Editor.
2. Select your data source.
3. Add a custom column (let's call it "Overlap Check") with the following formula:

= if List.Count(

            List.Select(

                Table.SelectRows(#"PreviousStep",

                    each [Uniq ID] = [Uniq ID] and

                         [From date] < [To date] and

                         [To date] > [From date]),

                each [Uniq ID] = [Uniq ID]

            )

        ) > 1 then "Yes" else "No doubles"

Replace `"PreviousStep"` with the name of your previous step.

4. This formula checks for each row if there are any other rows with the same Uniq ID where the date ranges overlap. If there are, it returns "Yes", otherwise "No doubles".

5. Close and load your query to see the results.

This should give you a column with "Yes" for overlapping date ranges and "No doubles" for rows with unique Uniq IDs. Adjust the column references and table names as needed for your dataset.

 

[p45cal]

@Susanne72 , a workbook with some realistic data in would be a great help and would save us guessing (wrongly) your setup.
While waiting for that, in the attached I've set up the bare bones of what I imagine your data looks like and added a Power Query query.
I've probably gone overboard and got a lot of things wrong but to identify clearly what overlaps with what, I added an index column (Idx) to your columns and have used that in the query to identify which rows overlap.
This will need a lot of streamlining
.

 

[Susanne72]

@Susanne72 , a workbook with some realistic data in would be a great help and would save us guessing (wrongly) your setup.
While waiting for that, in the attached I've set up the bare bones of what I imagine your data looks like and added a Power Query query.
I've probably gone overboard and got a lot of things wrong but to identify clearly what overlaps with what, I added an index column (Idx) to your columns and have used that in the query to identify which rows overlap.
This will need a lot of streamlining
.
View attachment 86682

Hey,
It seems to be vey usefull to me. Only thing is that I have only been supplied with Excel 2016, and in there the Replacer function is not available Can you think of an alterntaive? Sorry for not having mentioned this in my post in the first place.
Sorry for replying late but I really am trying everything out including searches and AI trying to figure next step out myself.

 

[Susanne72]

Here's a step-by-step guide:

1. Open your Power Query Editor.
2. Select your data source.
3. Add a custom column (let's call it "Overlap Check") with the following formula:

= if List.Count(

            List.Select(

                Table.SelectRows(#"PreviousStep",

                    each [Uniq ID] = [Uniq ID] and

                         [From date] < [To date] and

                         [To date] > [From date]),

                each [Uniq ID] = [Uniq ID]

            )

        ) > 1 then "Yes" else "No doubles"

Replace `"PreviousStep"` with the name of your previous step.

4. This formula checks for each row if there are any other rows with the same Uniq ID where the date ranges overlap. If there are, it returns "Yes", otherwise "No doubles".

5. Close and load your query to see the results.

This should give you a column with "Yes" for overlapping date ranges and "No doubles" for rows with unique Uniq IDs. Adjust the column references and table names as needed for your dataset.

Hey. Thanks for this. I did try it but it had my Excel crash several times during the proces. It seems to be too heavy somehow.

 

[p45cal]

Only thing is that I have only been supplied with Excel 2016, and in there the Replacer function is not available

This surprises me a lot! I'm not aware that it isn't available in 2016; I think it is. Could you point me to some data on this on the interweb?
Does the result table in the file I attached refresh properly?
If not, you could try removing the last step of the query (Replaced Value), it only puts 'no doubles' in cells which are blank.

Again, attach a workbook!

re:

out including searches and AI trying to figure next step out myself

The responses from Monty seem to be be AI solutions and ones which haven't been checked out well. They do not even begin to do what you want.