Restructuring Data - Show targets and their status

[Anonymous]

Hi,

I am currently trying to wrap my head around a little problem that I simply cannot get working properly.

I have a large excel table containing target fulfilment for different entities. The structure of this data is as follows:

Name	ID	Task 1 Description	Task 1 Completed?	Task 2 Description	Task 2 Completed?	Task 3 Description	Task 3 Completed?
John Smith	1	Buy a house	Yes	Build a car	No	Run a marathon	Yes
Ryan Miller	2	Buy a car	No	Plant a tree	Yes	Walk your dog	No

In my power BI report I however want to show the data in the following format:

[Filtered by John Smith]

Task	Completed?
Buy a house	Yes
Build a car	No
Run a marathon	Yes

[Filtered by Ryan Miller]

Task	Completed?
Buy a car	No
Plant a tree	Yes
Walk your dog	No

Does any one know a clever little trick to manipulate the table so that this will work? Or is there a good way to do this in the current format?

The idea behind this is to share targets and their status with different team members. The idea is to have a matrix/table with the information as shown above and a slicer to filter by name.

Thanks in advance!

S

[CNENFRNL]

Alternative solution

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIzs0syVDSUTIEYqfSSoVEhYz80uJUIC8ytRgslpmTAhRNTiwC8vzygURQaR5QIDexKLEkIz8PqjJWJ1opqDIxT8E3MycnFaTWCG4ikt6AnMS8EqBQSVEqwo7wxJxshcr80iKFlPx0iMrYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, ID = _t, #"Task 1 Description" = _t, #"Task 1 Completed?" = _t, #"Task 2 Description" = _t, #"Task 2 Completed?" = _t, #"Task 3 Description" = _t, #"Task 3 Completed?" = _t]),
    Custom1 = List.Transform(
        Table.ToRows(Source),
        each
        let
            hdr = List.FirstN(_, 2)
        in
            Table.FromRows(
                List.TransformMany({_}, each List.Split(List.Skip(_, 2), 2), (x, y) => hdr & y),
                {"Name", "ID", "Task", "Completed?"}
            )
    ),
    Custom2 = Table.Combine(Custom1)
in
    Custom2

[Anonymous]

Thanks - that worked out perfectly!

[Jakinta · Accepted solution]

This might help...

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name", "Task 1 Description", "Task 2 Description", "Task 3 Description"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Task", each if Text.Contains([Attribute], "Task 1") then [Task 1 Description]
else if Text.Contains([Attribute], "Task 2") then [Task 2 Description]
else if Text.Contains([Attribute], "Task 3") then [Task 3 Description]
else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Task 1 Description", "Task 2 Description", "Task 3 Description", "Attribute"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Name", "Task", "Value"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Task] <> null)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Completed ?"}})
in
#"Renamed Columns"