Forum Discussion
Auto generate Table by data and condition from other table
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.
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 🙂
8 Replies
- Fowmy
Super User
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 🙂
- Greg_Deckler
Community Champion
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
Greg_Deckler Thank you so much , its worked like my way!!
- amitchandak
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? : (
- Fowmy
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 🙂
amitchandak Is it possible to only use it as DAX?
