extract data to another sheet with multiple conditions?

    This is an old one but I never got it solved so maybe someone knows the solution, it would be useful again... Every id on sheet1 has one or more datasets and these datasets consist information that belong to a certain type (on sheet2). These types are: Type1= Ddate, Reason. Type2=usedD1, usedD2, usedD3, endDate, Type3=Other.
    This information on sheet1 should be extracted to sheet2 according to these types, so data originally in one row could be splitted on multiple different rows on sheet2.
    Hopefully this attachement is clear enough. There is one sheet, on top is shortened version of sheet1 (original) and below is sheet2 (what it should look like at the end):



    [ATTACH]n1219078[/ATTACH]

    Would this presentation work for you? If yes, then let me know and I will advise how I achieved this using Power Query functionality in to Excel.


    Data Range [TABLE="class: grid"]

    [tr]


    [td][/td]


    [td]

    A


    [/td]


    [td]

    B


    [/td]


    [td]

    C


    [/td]


    [td]

    D


    [/td]


    [td]

    E


    [/td]


    [td]

    F


    [/td]


    [td]

    G


    [/td]


    [/tr]


    [tr]


    [td]

    1


    [/td]


    [td]

    ID


    [/td]


    [td]

    Type


    [/td]


    [td]

    Ddate


    [/td]


    [td]

    Reason


    [/td]


    [td]

    Other


    [/td]


    [td]

    Value


    [/td]


    [td]

    endDate


    [/td]


    [/tr]


    [tr]


    [td]

    2


    [/td]


    [td]

    101


    [/td]


    [td]

    1


    [/td]


    [td]

    9/27/2017


    [/td]


    [td]

    1


    [/td]


    [td]

    A


    [/td]


    [td]

    SK152


    [/td]


    [td]

    9/26/2016


    [/td]


    [/tr]


    [tr]


    [td]

    3


    [/td]


    [td]

    101


    [/td]


    [td]

    2


    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    SK153


    [/td]


    [td]

    4/11/2017


    [/td]


    [/tr]


    [tr]


    [td]

    4


    [/td]


    [td]

    101


    [/td]


    [td]

    2


    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    SK154


    [/td]


    [td]

    4/11/2017


    [/td]


    [/tr]


    [tr]


    [td]

    5


    [/td]


    [td]

    101


    [/td]


    [td]

    3


    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    SK155


    [/td]


    [td]

    7/4/2017


    [/td]


    [/tr]


    [tr]


    [td]

    6


    [/td]


    [td]

    101


    [/td]


    [td]

    3


    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    SK156


    [/td]


    [td]

    7/4/2017


    [/td]


    [/tr]


    [tr]


    [td]

    7


    [/td]


    [td]

    101


    [/td]


    [td]

    3


    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    SK225


    [/td]


    [td]

    7/4/2017


    [/td]


    [/tr]


    [tr]


    [td]

    8


    [/td]


    [td]

    101


    [/td]


    [td]

    4


    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    SK226


    [/td]


    [td]

    7/5/2017


    [/td]


    [/tr]


    [tr]


    [td]

    9


    [/td]


    [td]

    102


    [/td]


    [td]

    1


    [/td]


    [td]

    10/1/2015


    [/td]


    [td][/td]


    [td]

    B,C


    [/td]


    [td]

    SK156


    [/td]


    [td]

    11/13/2013


    [/td]


    [/tr]


    [tr]


    [td]

    10


    [/td]


    [td]

    102


    [/td]


    [td]

    2


    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    SK157


    [/td]


    [td]

    11/14/2013


    [/td]


    [/tr]


    [tr]


    [td]

    11


    [/td]


    [td]

    102


    [/td]


    [td]

    2


    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    SK158


    [/td]


    [td]

    11/14/2013


    [/td]


    [/tr]


    [tr]


    [td]

    12


    [/td]


    [td]

    103


    [/td]


    [td]

    1


    [/td]


    [td]

    10/2/2015


    [/td]


    [td]

    4


    [/td]


    [td][/td]


    [td]

    SK157


    [/td]


    [td]

    11/1/2013


    [/td]


    [/tr]


    [tr]


    [td]

    13


    [/td]


    [td]

    103


    [/td]


    [td]

    1


    [/td]


    [td]

    10/2/2015


    [/td]


    [td]

    4


    [/td]


    [td][/td]


    [td]

    SK158


    [/td]


    [td]

    11/1/2013


    [/td]


    [/tr]


    [/TABLE]

    Thank you very much, this seems to be exactly what I want. I have tried Power Query only couple times so it quite strange to me...
    /Zak

    Here is the Mcode and I have attached the file so that you can review what I have done.


    Code
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Dataset", "endDate", "Ddate", "Reason", "Other"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"endDate", type date}, {"Ddate", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Dataset", "Type"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"ID", "Type", "Ddate", "Reason", "Other", "Attribute", "Value", "endDate"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Attribute"})
in
    #"Removed Columns"

    I noticed that result wasn't quite what I wanted after all... It gives wrong outcome because Dataset is not same as Type. Dataset changes every time when usedD is changed or modified. Type is used on second sheet to reorganize this data, and each row can contain only that data which is connected to type number (Type1 includes Ddate and Reason, Type 2 includes usedD1+usedD2+usedD3 (these three should also be in a same cell) and endDate, Type 3 includes Other). I tried but didn't mmanage to organize these events by id and type.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login