"From-To" date table to date table by year

[mizaskun]

Good morning,

I have a table with user regions from a date to a date.

USER  |  REGION  |  FROM  |  TO
A  |  EUROPE  |  20190101  |  99991231

A  |  INDIA  |  20190101  |  20201231

I need to join the table by year and user. Thus, I am trying to come up with a way to convert this table to a table by year:

USER  |  REGION  |  YEAR

A  |  EUROPE  |  2019

A  |  EUROPE  |  2020

A  |  EUROPE  |  2021

A  |  INDIA  |  2019

A  |  INDIA  |  2020

How could I do this? Is there any way to join the first table by FROM and TO? Table A inner join Table B on A.User = B.User and A.From <= B.Date and A.To > B.Date. I do not know how to join this way in Power BI.

Thank you so much!

[Vera_33 · Accepted solution]

Hi @mizaskun 

There are ways to do it, I don't have your TableB, pretend use Year, use User and Region to do Merge, then check the year

[YEAR]>=Date.Year( [TableA][FROM]{0})
and
[YEAR]<=Date.Year( [TableA][TO]{0})

Or go to your TableA, convert From to To as a list, then Merge two tables, check the if date (in number format) in the list

{Number.From([FROM])..Number.From([TO])}