To pull row data into columns

[Jithin00]

Below Table 1 is my Input data and Table 2 is the required output formart  in powerBI where i can filter on Name column.

Tried with unpivot and creating 2 tables for Task's and Feedback but the requrment is to create only 1 table at the output.

 any Alternative methods? 

TABLE 1
Name	Task1	Task2	Task3	Feedback1	Feedback3	Feedback3
Adit	9.6	8	9	good	nice	excelent
Shashank	8	8.9	10	nice	Very good	Perfect

TABLE 2
Adit	TASK1	9.6	good
Adit	TASK2	8	nice
Adit	TASK3	9	excelent
Shashank	TASK1	8	nice
Shashank	TASK2	8.9	very good
Shashank	TASK3	10	perfect

[ryan_mayu · Accepted solution]

@Jithin00 

you can try this.

1. merget columns

="Task1"&"-"&[Task1]&"-"&[Feedback1]&";"&"Task2"&"-"&[Task2]&"-"&[Feedback2]&";"&"Task3"&"-"&[Task3]&"-"&[Feedback3]

2. remove useless columns

3. split column by delimiter to rows

4. split colmn by delimiter again.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckzJLFHSUbLUMwOSFiAWEKfn56cAqbzM5FQglVqRnJqTmleiFKsTrRSckVickZiXDVVtoQdSb2iAUB2WWlSpADUgILUoLTUZqDEWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Task1 = _t, Task2 = _t, Task3 = _t, Feedback1 = _t, Feedback2 = _t, Feedback3 = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each "Task1"&"-"&[Task1]&"-"&[Feedback1]&";"&"Task2"&"-"&[Task2]&"-"&[Feedback2]&";"&"Task3"&"-"&[Task3]&"-"&[Feedback3]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Task1", "Task2", "Task3", "Feedback1", "Feedback2", "Feedback3"}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns", {{"Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Custom", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Name", type text}, {"Custom.1", type text}, {"Custom.2", type number}, {"Custom.3", type text}})
in
#"Changed Type"

[Jithin00]

Thankyou for your response, this method works.

[ryan_mayu]

you are welcome