Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Consider Table1 below , also note I'm not able to use Power Query currently for below report.
user_key | FYear |
001 | 14/15 Q4 |
002 | 15/16 Q1 |
003 | 15/16 Q1 |
004 | 15/16 Q2 |
I want to create a new table (Table2) with the format below. It will capture all discint values from Table1[FYear] column.
@ddecker
FYear | idx |
14/15 Q4 | 1 |
15/16 Q1 | 2 |
15/16 Q2 | 3 |
Solved! Go to Solution.
Hi, @giordafrancis
I only know how to create this table in two steps. I could not find a way to create it in one step.
step 1. create a Table
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @giordafrancis
I only know how to create this table in two steps. I could not find a way to create it in one step.
step 1. create a Table
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Here's how to do it in one go:
[New Table] =
addcolumns(
distinct( YourTable[FYear] ),
"idx",
var CurrentFYear = YourTable[FYear]
return
countrows(
summarize(
filter(
YourTable,
YourTable[FYear] <= CurrentFYear
),
YourTable[FYear]
)
)
)