Forum Discussion

SarahAnn's avatar
SarahAnn
New Member
4 years ago
Solved

Unpivot in Power Query

Hi Everyone,

 

I am pretty new to Power Query and my data is in a format that makes it very hard to use. I want to unpivot it but cannot quite figure out how to do what I am needing to do. The data is in the below format. I have a ton of launch countries that all go from left to right and then the data associated with the country is in its own column (so launch country 1 is the US and all data with a 1 in the header is associated with the US, while all data with 2 in the header is associated with Canada). Some of the data (like Project numbers, project name and review date) are not country-specific, but project specific. 

 

Project NumberProjectNameModifiedLaunchCountryCode1LaunchDate1LaunchCountryCode2LaunchDate2LaunchCountryCode3LaunchDate3LaunchCountryCode4LaunchDate4PrelaunchDate1PrelaunchDate2PrelaunchDate3PrelaunchDate4PlantoLaunch1PlantoLaunch2PlantoLaunch3PlantoLaunch4Review Date
1Project A1/1/2021US1/1/2021Canada1/1/2021Mexico1/1/2021Israel1/1/20211/1/20211/1/20211/1/20211/1/2021 TRUEFALSETRUEFALSE1/1/2021
2Project B2/1/2021US2/1/2021Canada2/1/2021Mexico2/1/2021Israel2/1/20212/1/20212/1/20212/1/20212/1/2021 TRUEFALSETRUEFALSE1/1/2021
3Project C3/1/2021US3/1/2021Canada3/1/2021Mexico3/1/2021Israel3/1/20213/1/20213/1/20213/1/20213/1/2021 TRUEFALSETRUEFALSE1/1/2021
4Project D4/1/2021US4/1/2021Canada4/1/2021Mexico4/1/2021Israel4/1/20214/1/20214/1/20214/1/20214/1/2021 TRUEFALSEFALSETRUE1/1/2021
5Project E5/1/2021US5/1/2021Canada5/1/2021Mexico5/1/2021Israel5/1/20215/1/20215/1/20215/1/20215/1/2021 TRUEFALSEFALSETRUE1/1/2021

 

I want to put all launch dates in one column and just repeat data that is not country specific next to it, like the below reformatted table:

 

Project NumberProjectNameModifiedLaunch CountryLaunch DatePrelaunch DatePlan to Launch?Review Date
1Project A1/1/2020US1/1/20211/1/2021TRUE1/1/2021
2Project B2/1/2020US2/1/20212/1/2021TRUE1/1/2021
3Project C3/1/2020US3/1/20213/1/2021TRUE1/1/2021
4Project D4/1/2020US4/1/20214/1/2021TRUE1/1/2021
5Project E5/1/2020US5/1/20215/1/2021TRUE1/1/2021
1Project A1/1/2020Canada1/1/20211/1/2021FALSE1/1/2021
2Project B2/1/2020Canada2/1/20212/1/2021FALSE1/1/2021
3Project C3/1/2020Canada3/1/20213/1/2021FALSE1/1/2021
4Project D4/1/2020Canada4/1/20214/1/2021FALSE1/1/2021
5Project E5/1/2020Canada5/1/20215/1/2021FALSE1/1/2021
1Project A1/1/2020Mexico1/1/20211/1/2021TRUE1/1/2021
2Project B2/1/2020Mexico2/1/20212/1/2021TRUE1/1/2021
3Project C3/1/2020Mexico3/1/20213/1/2021TRUE1/1/2021
4Project D4/1/2020Mexico4/1/20214/1/2021FALSE1/1/2021
5Project E5/1/2020Mexico5/1/20215/1/2021FALSE1/1/2021
1Project A1/1/2020Israel1/1/20211/1/2021FALSE1/1/2021
2Project B2/1/2020Israel2/1/20212/1/2021FALSE1/1/2021
3Project C3/1/2020Israel3/1/20213/1/2021FALSE1/1/2021
4Project D4/1/2020Israel4/1/20214/1/2021TRUE1/1/2021
5Project E5/1/2020Israel5/1/20215/1/2021TRUE1/1/2021

 

I just cannot quite figure out the steps for this, if anyone can help, it would be so appreciated!

Need Help
  • HotChilli's avatar
    HotChilli
    4 years ago

    Move the Review Date column to the left (in 2nd position).

    Select the 1st four columns and Unpivot Other Columns.

    Split the Attribute column (from non-digit to digit).

    Select the Attribute1 column and Pivot (with Value in Values) and Don't Aggregate from the Advanced.

     

    --

    Let me know how it goes

     

  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Unpivot every column ending in a digit, split the digit off of the end (Home > Transform > Spit Column > By Non-Digit to Digit), and then re-pivot.

     

    After pivoting and splitting:

     

    Full code you can paste into the Advanced Editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lc+xCsIwEMbxVymZCzWX5AFqVRAUxNopdAg1gyIWqoOPb7LI5bjBm/LP1w6/eK+0qtVpme9xeldtat3oBlaQ56Ev7114hmsot2P83Ka53PavJcRHuUmyupyHbTp37aHPJ7n+/hxrrwDx16mB8IHhA8MHho82SUr4BvG71IbwDcM3DN8wfLRJUsK3iL9JbQnfMnzL8C3DR5skKZ88o+A7xM8fHeE7hu8YvmP4aJPk3/zxCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Number" = _t, ProjectName = _t, Modified = _t, LaunchCountryCode1 = _t, LaunchDate1 = _t, LaunchCountryCode2 = _t, LaunchDate2 = _t, LaunchCountryCode3 = _t, LaunchDate3 = _t, LaunchCountryCode4 = _t, LaunchDate4 = _t, PrelaunchDate1 = _t, PrelaunchDate2 = _t, PrelaunchDate3 = _t, PrelaunchDate4 = _t, PlantoLaunch1 = _t, PlantoLaunch2 = _t, PlantoLaunch3 = _t, PlantoLaunch4 = _t, #"Review Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Number", Int64.Type}, {"ProjectName", type text}, {"Modified", type date}, {"LaunchCountryCode1", type text}, {"LaunchDate1", type date}, {"LaunchCountryCode2", type text}, {"LaunchDate2", type date}, {"LaunchCountryCode3", type text}, {"LaunchDate3", type date}, {"LaunchCountryCode4", type text}, {"LaunchDate4", type date}, {"PrelaunchDate1", type date}, {"PrelaunchDate2", type date}, {"PrelaunchDate3", type date}, {"PrelaunchDate4", type date}, {"PlantoLaunch1", type logical}, {"PlantoLaunch2", type logical}, {"PlantoLaunch3", type logical}, {"PlantoLaunch4", type logical}, {"Review Date", type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project Number", "ProjectName", "Modified", "Review Date"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute]), "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"LaunchCountryCode", type text}, {"LaunchDate", type date}, {"PrelaunchDate", type date}, {"PlantoLaunch", type logical}})
in
    #"Changed Type1"

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    Unpivot every column ending in a digit, split the digit off of the end (Home > Transform > Spit Column > By Non-Digit to Digit), and then re-pivot.

     

    After pivoting and splitting:

     

    Full code you can paste into the Advanced Editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lc+xCsIwEMbxVymZCzWX5AFqVRAUxNopdAg1gyIWqoOPb7LI5bjBm/LP1w6/eK+0qtVpme9xeldtat3oBlaQ56Ev7114hmsot2P83Ka53PavJcRHuUmyupyHbTp37aHPJ7n+/hxrrwDx16mB8IHhA8MHho82SUr4BvG71IbwDcM3DN8wfLRJUsK3iL9JbQnfMnzL8C3DR5skKZ88o+A7xM8fHeE7hu8YvmP4aJPk3/zxCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Number" = _t, ProjectName = _t, Modified = _t, LaunchCountryCode1 = _t, LaunchDate1 = _t, LaunchCountryCode2 = _t, LaunchDate2 = _t, LaunchCountryCode3 = _t, LaunchDate3 = _t, LaunchCountryCode4 = _t, LaunchDate4 = _t, PrelaunchDate1 = _t, PrelaunchDate2 = _t, PrelaunchDate3 = _t, PrelaunchDate4 = _t, PlantoLaunch1 = _t, PlantoLaunch2 = _t, PlantoLaunch3 = _t, PlantoLaunch4 = _t, #"Review Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Number", Int64.Type}, {"ProjectName", type text}, {"Modified", type date}, {"LaunchCountryCode1", type text}, {"LaunchDate1", type date}, {"LaunchCountryCode2", type text}, {"LaunchDate2", type date}, {"LaunchCountryCode3", type text}, {"LaunchDate3", type date}, {"LaunchCountryCode4", type text}, {"LaunchDate4", type date}, {"PrelaunchDate1", type date}, {"PrelaunchDate2", type date}, {"PrelaunchDate3", type date}, {"PrelaunchDate4", type date}, {"PlantoLaunch1", type logical}, {"PlantoLaunch2", type logical}, {"PlantoLaunch3", type logical}, {"PlantoLaunch4", type logical}, {"Review Date", type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project Number", "ProjectName", "Modified", "Review Date"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute]), "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"LaunchCountryCode", type text}, {"LaunchDate", type date}, {"PrelaunchDate", type date}, {"PlantoLaunch", type logical}})
in
    #"Changed Type1"
  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    Move the Review Date column to the left (in 2nd position).

    Select the 1st four columns and Unpivot Other Columns.

    Split the Attribute column (from non-digit to digit).

    Select the Attribute1 column and Pivot (with Value in Values) and Don't Aggregate from the Advanced.

     

    --

    Let me know how it goes

     

  • SarahAnn's avatar
    SarahAnn
    New Member
    4 years ago

    AlexisOlson , HotChilli : Thank you both so much! This is wonderful and so helpful and it worked like a charm. I have been fighting with this data for a year now in its horrendous, barely usable format and your insights fixed my problem in just minutes. It is so appreciated, you both rock.

  • Stev_data's avatar
    Stev_data
    Frequent Visitor
    4 years ago

    Hi guys,

    I have recently started using Powerquery and have managed to filter the data below to an extent. I am trying to show only one column header for for the repeated row headers  'File No, Customer, Cct Defect Qty etc (i.e from column 4-15).

    For the column labeled 'Value', I need the 'Defect:..' to be shown in a seperate column, labeled defects and the 'W/O No.' to be the main column heading for the repeated row headers. Any idea how I can approach it as I have a large amount of data and cannot physically cut and paste them to get the desired outcome. I have attached the excel file as well to help understand what I am trying to do.

    If I could find a way to automatically populate the 'Defect:...' into a new column I feel I could easily split the column using the colon delimiter

     

     

     

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    Stev_data welcome to the forum.

    I suggest you post this question as a new post (rather than tagging on to this existing solved post).

    Please post the data as text and show your desired outcome. Also, it's not clear what result you want from the Value column so please show an example