Pivot and Grouping in powerbi

[Anonymous]

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 No	Assinged to	Taken	Taken	Questions	Answers
1	Jancy	Minali	05-07-21	How did we do	1
1	Jancy	Minali	05-07-21	Great Communication	TRUE
1	Jancy	Minali	05-07-21	Great Support	FALSE
1	Jancy	Minali	05-07-21	Overall	TRUE
2	Vigzor	Martin	03-07-21	How did we do	2
2	Vigzor	Martin	03-07-21	Great Communication	TRUE
2	Vigzor	Martin	03-07-21	Great Support	TRUE
2	Vigzor	Martin	03-07-21	Overall	TRUE
3	Mike	Firoz	07-07-21	How did we do	3
3	Mike	Firoz	07-07-21	Great Communication	TRUE
3	Mike	Firoz	07-07-21	Great Support	FALSE
3	Mike	Firoz	07-07-21	Overall	TRUE
Output Required:
IPA No	Assinged to	Taken	Taken	How did we do	Great Communication	Great Support	Overall
1	Jancy	Minali	05-07-21	1	TRUE	FALSE	TRUE
2	Vigzor	Martin	03-07-21	2	TRUE	TRUE	TRUE
3	Mike	Firoz	07-07-21	3	TRUE	FALSE	TRUE

[CNENFRNL · Accepted solution]

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

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

Try the following steps:

pivot column:

then click ok：

fianl get :

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

[CNENFRNL · Accepted solution]

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