Forum Discussion
Create relationship: 1 date from 1 table being between 2 dates in 2 columns in another table
I found this thread. Pretty helpful in walking you through the processs in how to expand a table based upon two different date fields. Only thing to add is to change your field names you're referencing in the formula!
Pretty easy.
Can you assist me in how I should go about "Expanding" my Eligibility Table as you suggested? I'm willing to give it a shot.
"If you were to create a list of dates for each row in your 'Eligibility Data' table (From Eff Date to Term Date) and then expand them into new rows, would that result in too many rows to handle or would it be managable?"
I've never done this before. Essentially, what I think will end up happening is that an existing row with an [Eff Date] of 1/1/2019 and a [Term Date] of 1/3/2019 will populate with three rows. For example, a starting table in Power BI like this:
would turn into this?:
If this is what you mean...can you assit in walking me through how to transform my data to this?
Community ChampionAnonymous
Sorry to barge in this late in the discussion... I may be overlooking something here but I would assume that the idea behind your date table is:
1) Ensure it covers the whole range of dates possible within your tables, with unique and continuous dates. I guess the question is which column has the earliest date and which has the latest date.
You can work around this by referencing the tables to three new tables (one for each of the dates columns you need), removing all columns except the dates columns. Append the tables to create a single table ("New Dates Table") with one column for the dates ("Date"), remove the duplicates and order ascending.
You can then use the following code in a new query in Power Query to create your date table and to obtain a list of unique, continuous dates based on the MIN and MAX dates in this new table:
let
ChangedType = Table.TransformColumnTypes(#"New Dates Table",{{"Date", type date}}),
MaxDate = Record.Field(Table.Max(ChangedType,"Date"),"Date"),
MinDate = Record.Field(Table.Min(ChangedType,"Date"),"Date"),
DaysElapsed = Number.From(MaxDate-MinDate),
DateList = List.Dates(MinDate, DaysElapsed+1,Duration.From(1)),
#"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
and build any further columns you may need to have a working date table.
2) Create relationships with the dates fields in each table which you will be using most.
3) For calculations/filtering which require the date field not included in the active relationship, create an inactive relationship and use the USERELATIONSHIP function.
I apologise if I have misunderstood what your request is.