Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I am stuck with Pivoting columns in Power Query, Powerbi.
I have table 1 and want to create table 2 and table 3 (see example data below).
If a contact has a one year membership (ex. 2020-2020) he needs to be displayed in the pivoted column of that year (2020).
If a contact has a two year membership (ex. 2019-2020) he needs to be displayed in the pivoted columns of both years (2019 and 2020).
Currently I only manage to display the contact in one of the two columns, but not in both. Anyone knows how to do this ?
Table 1
ContactId | StartDate membership (Date type) | EndDate membership (Date type) | Other columns with membership info e.g. Type |
45689 | 2018 | 2019 | Trainee |
47546 | 2018 | 2018 | Active |
41598 | 2018 | 2019 | Trainee |
58964 | 2020 | 2021 | Retired |
68465 | 2020 | 2021 | Retired |
65465 | 2019 | 2020 | Active |
65465 | 2020 | 2020 | Active |
65469 | 2019 | 2020 | Active |
Table 2
ContactId | 2018 | 2019 | 2020 | 2021 |
45689 | Yes | Yes |
|
|
47546 | Yes |
|
|
|
41598 | Yes | Yes |
|
|
58964 |
|
| Yes | Yes |
68465 |
|
| Yes | Yes |
65465 |
| Yes | Yes |
|
65465 |
|
| Yes |
|
65469 |
| Yes | Yes |
|
Table 3
ContactId | 2018 | 2019 | 2020 | 2021 |
45689 | Yes, 2018-2019 Trainee | Yes, 2018-2019 Trainee |
|
|
47546 | Yes, 2018 Active |
|
|
|
41598 | Yes, 2018-2019 Trainee | Yes, 2018-2019 Trainee |
|
|
58964 |
|
| Yes, 2020-2021 Retired | Yes, 2020-2021 Retired |
68465 |
|
| Yes, 2020-2021 Retired | Yes, 2020-2021 Retired |
65465 |
| Yes, 2019-2020 Active | Yes, 2019-2020 Active |
|
65465 |
|
| Yes, 2020 Active |
|
65469 |
| Yes, 2019-2020 Active | Yes, 2019-2020 Active |
|
Solved! Go to Solution.
Hi, @DataInsights thank you for the feedback. Good to know how to do this in DAX too!
I found how to do this in Power Query with the use of This article from The Biccountant 🙂
Hi, @DataInsights thank you for the feedback. Good to know how to do this in DAX too!
I found how to do this in Power Query with the use of This article from The Biccountant 🙂
This can be done with DAX and a Date table (no relationship between the tables). See measures below:
Membership Year =
VAR vStartYear =
YEAR ( MAX ( Memberships[StartDate membership] ) )
VAR vEndYear =
YEAR ( MAX ( Memberships[EndDate membership] ) )
VAR vYear =
SELECTEDVALUE ( Dates[Year] )
VAR vResult =
IF ( vStartYear = vYear || vEndYear = vYear, "Yes" )
RETURN
vResult
Membership Year with Type =
VAR vStartYear =
YEAR ( MAX ( Memberships[StartDate membership] ) )
VAR vEndYear =
YEAR ( MAX ( Memberships[EndDate membership] ) )
VAR vYear =
SELECTEDVALUE ( Dates[Year] )
VAR vDisplayText = "Yes, " & vStartYear & "-" & vEndYear & " " & MAX ( Memberships[Type] )
VAR vResult =
IF ( vStartYear = vYear || vEndYear = vYear, vDisplayText )
RETURN
vResult
Create matrix visuals using Dates[Year] as columns:
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.