cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LP28D
Helper II
Helper II

Pivot and Grouping in powerbi

Hi team, 

 

I have the below sample input table and trying to achieve the output given below. I need help in understanding what are the steps to be taken to achieve the output. is it the pivot or grouping?

 

Input :        
IPA NoAssinged toTakenTakenQuestionsAnswers  
1JancyMinali05-07-21How did we do1  
1JancyMinali05-07-21Great CommunicationTRUE  
1JancyMinali05-07-21Great SupportFALSE  
1JancyMinali05-07-21OverallTRUE  
2VigzorMartin03-07-21How did we do2  
2VigzorMartin03-07-21Great CommunicationTRUE  
2VigzorMartin03-07-21Great SupportTRUE  
2VigzorMartin03-07-21OverallTRUE  
3MikeFiroz07-07-21How did we do3  
3MikeFiroz07-07-21Great CommunicationTRUE  
3MikeFiroz07-07-21Great SupportFALSE  
3MikeFiroz07-07-21OverallTRUE  
        
Output Required:       
IPA NoAssinged toTakenTakenHow did we doGreat CommunicationGreat SupportOverall
1JancyMinali05-07-211TRUEFALSETRUE
2VigzorMartin03-07-212TRUETRUETRUE
3MikeFiroz07-07-213TRUEFALSETRUE
2 ACCEPTED SOLUTIONS
CNENFRNL
Community Champion
Community Champion

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJKzEuuBNK+mXmJOZlAhoGproG5rhFIziO/XCElM0WhPFUhJR/IN1SK1SGsy70oNbFEwTk/N7c0LzM5sSQzPw8oGhIU6kqC9uDSgoL8ohIg383RJ5g4nf5lqUWJOTnIlhkBOWGZ6VX5RSA9iUUlmSC3GBjj9KIRcdoI+JFI/QhPEq8TiyeNweGRnQoKrMyi/CqQBnOcPjQmQg8B7xGjGVsE4teH7rNYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PA No" = _t, #"Assinged to" = _t, Taken = _t, Date = _t, Questions = _t, Answers = _t]),
    #"Grouped Rows" = Table.Group(Source, {"PA No", "Assinged to", "Taken", "Date"}, {{"ar", each _, type table [PA No=nullable text, Assinged to=nullable text, Taken=nullable text, Date=nullable text, Questions=nullable text, Answers=nullable text]}}),
    Pivot = Table.TransformColumns(#"Grouped Rows", {"ar", each Table.Pivot(_, List.Distinct([Questions]), "Questions", "Answers")}),
    Expanded = Table.Combine(Pivot[ar])
in
    Expanded

Screenshot 2021-07-08 172951.png

View solution in original post

v-luwang-msft
Community Support
Community Support

Hi @LP28D ,

Try the following steps:

vluwangmsft_0-1626165923268.png

 

pivot column:

vluwangmsft_1-1626165968546.png

 

then click ok:

fianl get :

vluwangmsft_2-1626165992313.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJKzEuuBNK+mXmJOZlAhoGproG5rhFIziO/XCElM0WhPFUhJR/IN1SK1SGsy70oNbFEwTk/N7c0LzM5sSQzPw8oGhIU6kqC9uDSgoL8ohIg383RJ5g4nf5lqUWJOTnIlhkBOWGZ6VX5RSA9iUUlmSC3GBjj9KIRcdoI+JFI/QhPEq8TiyeNweGRnQoKrMyi/CqQBnOcPjQmQg8B7xGjGVsE4teH7rNYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"IPA No" = _t, #"Assinged to" = _t, Taken = _t, Taken.1 = _t, Questions = _t, Answers = _t]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Questions]), "Questions", "Answers")
in
    #"Pivoted Column"

 

Wish it is helpful for you!

 

Best Regards

Lucien

View solution in original post

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

Hi @LP28D ,

Try the following steps:

vluwangmsft_0-1626165923268.png

 

pivot column:

vluwangmsft_1-1626165968546.png

 

then click ok:

fianl get :

vluwangmsft_2-1626165992313.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJKzEuuBNK+mXmJOZlAhoGproG5rhFIziO/XCElM0WhPFUhJR/IN1SK1SGsy70oNbFEwTk/N7c0LzM5sSQzPw8oGhIU6kqC9uDSgoL8ohIg383RJ5g4nf5lqUWJOTnIlhkBOWGZ6VX5RSA9iUUlmSC3GBjj9KIRcdoI+JFI/QhPEq8TiyeNweGRnQoKrMyi/CqQBnOcPjQmQg8B7xGjGVsE4teH7rNYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"IPA No" = _t, #"Assinged to" = _t, Taken = _t, Taken.1 = _t, Questions = _t, Answers = _t]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Questions]), "Questions", "Answers")
in
    #"Pivoted Column"

 

Wish it is helpful for you!

 

Best Regards

Lucien

View solution in original post

CNENFRNL
Community Champion
Community Champion

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJKzEuuBNK+mXmJOZlAhoGproG5rhFIziO/XCElM0WhPFUhJR/IN1SK1SGsy70oNbFEwTk/N7c0LzM5sSQzPw8oGhIU6kqC9uDSgoL8ohIg383RJ5g4nf5lqUWJOTnIlhkBOWGZ6VX5RSA9iUUlmSC3GBjj9KIRcdoI+JFI/QhPEq8TiyeNweGRnQoKrMyi/CqQBnOcPjQmQg8B7xGjGVsE4teH7rNYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PA No" = _t, #"Assinged to" = _t, Taken = _t, Date = _t, Questions = _t, Answers = _t]),
    #"Grouped Rows" = Table.Group(Source, {"PA No", "Assinged to", "Taken", "Date"}, {{"ar", each _, type table [PA No=nullable text, Assinged to=nullable text, Taken=nullable text, Date=nullable text, Questions=nullable text, Answers=nullable text]}}),
    Pivot = Table.TransformColumns(#"Grouped Rows", {"ar", each Table.Pivot(_, List.Distinct([Questions]), "Questions", "Answers")}),
    Expanded = Table.Combine(Pivot[ar])
in
    Expanded

Screenshot 2021-07-08 172951.png

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!