how to rebuild periods (with non-contiguous Periods and with overlaps)

I

ivan00

New member
Joined
Mar 4, 2017
Messages
6
Reaction score
0
Points
0
Location
Genova (Italia)
Excel Version(s)
Office 2013
Hello,
I would ask help because I can not understand how to solve a problem related to a table that manages periods of different dates (also non contiguous and overlap). I would like to rebuild these periods (or to expand these lines), using existing dates, even when the attribute shown is the same.
I tried to help me with adding two index (from 0 and from 1) but I can not understand the logic to apply.
Can you please help me?

Here below an example that can better explain my problem.
Scenario1.PNG
Thanks again for the support
Ivan
 
Hello,

Firstly, I have not yet realized what the desired result should look like and secondly, I have tried to import your image into a Power Query to possibly be able to create the desired result.

But unfortunately my O365 Power Query is not able to import images.

Okay, I think I understand the task now, but images still cannot be imported by my Power Query.
 
Last edited:
Hi,
I try to paste table below, thanks.
Code: 
let 
  Source = Table.FromRecords(
    {  

[Name="John", StartDate="16/04/2014", EndDate="31/08/2023", Attribute="B"],
[Name="John", StartDate="01/05/2021", EndDate="31/12/9999", Attribute="A"],
[Name="John", StartDate="01/01/2022", EndDate="31/07/2024", Attribute="A"],
[Name="Peter", StartDate="17/04/2014", EndDate="31/07/2021", Attribute="A"],
[Name="Peter", StartDate="01/01/2022", EndDate="31/08/2023", Attribute="A"]

    }
  ),
    #"Modificato tipo" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}, {"Name", type text}, {"Attribute", type text}})
in
  #"Modificato tipo"
 
Now that I've done some work on the problem, I find that I can't derive the transformation rules (3 input lines John to 12 output lines) after all.

You seem to assume that this is clear as daylight for everyone. In this respect it can be due to me that I do not understand it.

So I hope for you that someone else can derive the logic correctly and is able to create the desired Power Query for you.
 
You must log in or register to reply here.
Back
Top