cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
moscholes
Helper V
Helper V

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-JanJame11  
01-JanKelly 1  
02-JanJame1   
03-JanJame  1 
02-JanKelly  11

 

Table2 (auto generate)

Date                name               Suject
01-JanJamelesson1
01-JanJamelesson2
01-JanKellylesson2
02-JanJamelesson1
03-JanJamelesson3
02-JanKellylesson3
02-JanKellylesson4

 

Thanks a lot in advance for your help.

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

@moscholes 

.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.

Fowmy_0-1596958848685.png

 

________________________

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

8 REPLIES 8
amitchandak
Super User IV
Super User IV

@moscholes , refer if this can help

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

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

@moscholes 

.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.

Fowmy_0-1596958848685.png

 

________________________

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

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

Greg_Deckler
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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

Fowmy
Super User IV
Super User IV

@moscholes 

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,

Fowmy_1-1596907744445.png


2. Click on the filter button on the Value column and deselect blank, done. You can remove the value column.

Fowmy_2-1596907778387.png
3. Result

Fowmy_0-1596907694323.png



________________________

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

 

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.