Auto generate Table by data and condition from other table

[Anonymous]

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.

[Greg_Deckler · Accepted 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

[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/

[Anonymous]

@amitchandak Is it possible to only use it as DAX?

[Anonymous]

@Fowmy  thank for you advice!! but all lesson column in Table is calculated columnม Is it possible to use it as DAX? : (

[Fowmy · Accepted solution]

@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 🙂

YouTube, LinkedIn

[Anonymous]

@Fowmy Thank you so much for your nice description and details , i have tested it and its great : )

[Greg_Deckler · Accepted 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]

@Greg_Deckler Thank you so much , its worked like my way!!

[Fowmy]

@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 🙂

YouTube, LinkedIn