Excel columns with double date range headings

[Dee]

Hi all,

I am trying to import data from excel which happens to have double headings. One is the date range in quarter, and the other is the quarter range as shown below.

How do I make it such that I transpose the columns, and still maintain the right headings In date format?

2017/18 Q1

2017/18 Q2

2017/18 Q3

2017/18 Q4

2019/20 Q1

2019/20 Q2

2019/20 Q3

2019/20 Q4

2019/20 Q1

2019/20 Q2

2019/20 Q3

2019/20 Q4

2020/21 Q1

2020/21 Q2

2020/21 Q3

2020/21 Q4

A

B

C

D

Jul'17- Sep'17

Oct'17- Dec'17

Jan'18 - Mar'18

Apr'18- Jun'18

Jul'18- Sep'18

Oct'18- Dec'18

Jan'19 - Mar'19

Apr'19- Jun'19

Jul'19- Sep'19

Oct'19- Dec'19

Jan'20 - Mar'20

Apr'20- Jun'20

Jul'20- Sep'20

Oct'20- Dec'20

Jan'21 - Mar'21

Apr'21- Jun'21

I would really appreciate any help in solving this.

TIA

[v-kelly-msft · Accepted solution]

Hi @Dee,

Create 2 calculated columns as below:

year & quarter = YEAR('Table'[Date])&"Q"&QUARTER('Table'[Date])

Month Year = 
VAR minmonth=CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[year & quarter]=EARLIER('Table'[year & quarter])))
VAR maxmonth=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[year & quarter]=EARLIER('Table'[year & quarter])))
Return
FORMAT(minmonth,"MMM")&"'"&FORMAT(minmonth,"YY")&"-"&FORMAT(maxmonth,"MMM")&"'"&FORMAT(maxmonth,"YY")

Create a matirx and you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[BA_Pete]

Hi @Dee ,

I would start by creating a calendar table with your Year/Qtr and Quarter Range dimensions programmatically defined against dates.

What I mean by this would be columns in your calendar table that, given the date in each row, would output which Year/Quarter that date pertains to, and/or which quarter month range that date pertains to.

I would then delete the top row of your Excel import to remove the Year/Qtr value.

Next, I'd multi-select the four values columns (A, B, C, D) and select 'Unpivot other columns' from the ribbon. This will put all of your quarter month ranges into one column.

I'd then then try to isolate the first month from your range to convert to a date. In Power Query you could extract text before delimiter "-" into a new column, then change this to Date type. PQ should be smart enough to convert this to dates showing the first date of each of your months e.g. 01/01/2020.

Next relate your new calendar table from calendar[Date] to your new "First day of the quarter date" field. Use your Year/Quarter and Quarter Month Range fields from your calendar table on any visuals that you want to show by these dimensions.

Pete