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 friend
I don't sure power bi will be able to do this.
But can someone advise me how to create table2 that all column auto generate data from table1 by each row are information from each lesson that value 1 individual by day
Table1
Date | name | lesson1 | lesson2 | lesson3 | lesson4 |
01-Jan | Jame | 1 | 1 | ||
01-Jan | Kelly | 1 | |||
02-Jan | Jame | 1 | |||
03-Jan | Jame | 1 | |||
02-Jan | Kelly | 1 | 1 |
Table2 (auto generate)
Date | name | Suject |
01-Jan | Jame | lesson1 |
01-Jan | Jame | lesson2 |
01-Jan | Kelly | lesson2 |
02-Jan | Jame | lesson1 |
03-Jan | Jame | lesson3 |
02-Jan | Kelly | lesson3 |
02-Jan | Kelly | lesson4 |
Thanks a lot in advance for your help.
Solved! Go to Solution.
@Anonymous - Just select your four lesson columns and unpivot them in Power Query. If you want a DAX method, there is a way to achieve unpivot in DAX also: https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/m-p/574832#M256
@Anonymous
.I used @Greg_Deckler's technique to union each column and filter then added a tweak to include column names.
You will have to hardcode our column names as I have done below.
LESSONS DAX =
FILTER(
UNION(
SELECTCOLUMNS( LESSONS , "DATE", [Date] , "NAME" , [name] , "LESSON", IF([lesson1] = "1", "Lesson 1", BLANK())),
SELECTCOLUMNS( LESSONS , "DATE", [Date] , "NAME" , [name] , "LESSON", IF([lesson2] = "1", "Lesson 2", BLANK())),
SELECTCOLUMNS( LESSONS , "DATE", [Date] , "NAME" , [name] , "LESSON", IF([lesson3] = "1", "Lesson 3", BLANK())),
SELECTCOLUMNS( LESSONS , "DATE", [Date] , "NAME" , [name] , "LESSON", IF([lesson4] = "1", "Lesson 4", BLANK()))
),
[LESSON] <> BLANK()
)
Result.
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous , refer if this can help
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/
@Fowmy thank for you advice!! but all lesson column in Table is calculated columnม Is it possible to use it as DAX? : (
@Anonymous
.I used @Greg_Deckler's technique to union each column and filter then added a tweak to include column names.
You will have to hardcode our column names as I have done below.
LESSONS DAX =
FILTER(
UNION(
SELECTCOLUMNS( LESSONS , "DATE", [Date] , "NAME" , [name] , "LESSON", IF([lesson1] = "1", "Lesson 1", BLANK())),
SELECTCOLUMNS( LESSONS , "DATE", [Date] , "NAME" , [name] , "LESSON", IF([lesson2] = "1", "Lesson 2", BLANK())),
SELECTCOLUMNS( LESSONS , "DATE", [Date] , "NAME" , [name] , "LESSON", IF([lesson3] = "1", "Lesson 3", BLANK())),
SELECTCOLUMNS( LESSONS , "DATE", [Date] , "NAME" , [name] , "LESSON", IF([lesson4] = "1", "Lesson 4", BLANK()))
),
[LESSON] <> BLANK()
)
Result.
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy Thank you so much for your nice description and details , i have tested it and its great : )
@Anonymous - Just select your four lesson columns and unpivot them in Power Query. If you want a DAX method, there is a way to achieve unpivot in DAX also: https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/m-p/574832#M256
@Anonymous
It's absolutely possible!
Easy to do it in Power Query with just two steps
1. Your data look like this at the beginning, Select Date and Name columns and right-click, select Unpivot Other Columns,
2. Click on the filter button on the Value column and deselect blank, done. You can remove the value column.
3. Result
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |