Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Restructuring Data - Show targets and their status

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. Th...
Need Help
Tips and Tricks
  • Jakinta's avatar
    Jakinta
    5 years ago

    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"

     

     

     

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
5 years ago

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