Create a custom index column (for month or week)

bkrol
Posts: 12
Joined: Tue Apr 16, 2019 8:52 am

Create a custom index column (for month or week)

Postby bkrol » Sun Apr 28, 2019 10:29 am

I am creating a custom calendar table that covers several year. I want to create an index for things like fiscal month or week.

If I was using excel and the fiscal month was in column A and the fiscal month index was in column B, then I would write a formula like =if(A2<>A1,B1+1,B1) and copy it down the sorted column.

Basically I want to add 1 if there is a change in value for a specific column.

I'm not sure how this is done in Power Query.

Thanks,

Bill

PhilC
Posts: 264
Joined: Tue Sep 09, 2014 8:13 am

Re: Create a custom index column (for month or week)

Postby PhilC » Wed May 01, 2019 8:36 pm

Have you read through this article?

https://exceleratorbi.com.au/build-reus ... wer-query/

It discusses creating a Fiscal Year (based on July 1), and also has columns for fiscal month.

This also might be of use:
https://community.powerbi.com/t5/Deskto ... d-p/556549

Cheers
Phil

bkrol
Posts: 12
Joined: Tue Apr 16, 2019 8:52 am

Re: Create a custom index column (for month or week)

Postby bkrol » Fri Jun 21, 2019 9:53 am

I wanted to add that the best solution to the problem that I described was here by Ken Puls
https://www.excelguru.ca/blog/2018/06/1 ... wer-query/

I used Ranking Method 4: Dense Rank

Sort the YYYYMM column in descending order
Go to Transform --> Group
Group by the YYYYMM column
Create the following column:
Data which uses the All Rows operation
Add an Index column from 1
Expand the Data column
Reorder the columns if desired


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 2 guests