Unpivot in Power Query

[SarahAnn]

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 Number

ProjectName

Modified

LaunchCountryCode1

LaunchDate1

LaunchCountryCode2

LaunchDate2

LaunchCountryCode3

LaunchDate3

LaunchCountryCode4

LaunchDate4

PrelaunchDate1

PrelaunchDate2

PrelaunchDate3

PrelaunchDate4

PlantoLaunch1

PlantoLaunch2

PlantoLaunch3

PlantoLaunch4

Review Date

1

Project A

1/1/2021

US

1/1/2021

Canada

1/1/2021

Mexico

1/1/2021

Israel

1/1/2021

1/1/2021

1/1/2021

1/1/2021

1/1/2021

TRUE

FALSE

TRUE

FALSE

1/1/2021

2

Project B

2/1/2021

US

2/1/2021

Canada

2/1/2021

Mexico

2/1/2021

Israel

2/1/2021

2/1/2021

2/1/2021

2/1/2021

2/1/2021

TRUE

FALSE

TRUE

FALSE

1/1/2021

3

Project C

3/1/2021

US

3/1/2021

Canada

3/1/2021

Mexico

3/1/2021

Israel

3/1/2021

3/1/2021

3/1/2021

3/1/2021

3/1/2021

TRUE

FALSE

TRUE

FALSE

1/1/2021

4

Project D

4/1/2021

US

4/1/2021

Canada

4/1/2021

Mexico

4/1/2021

Israel

4/1/2021

4/1/2021

4/1/2021

4/1/2021

4/1/2021

TRUE

FALSE

FALSE

TRUE

1/1/2021

5

Project E

5/1/2021

US

5/1/2021

Canada

5/1/2021

Mexico

5/1/2021

Israel

5/1/2021

5/1/2021

5/1/2021

5/1/2021

5/1/2021

TRUE

FALSE

FALSE

TRUE

1/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 Number	ProjectName	Modified	Launch Country	Launch Date	Prelaunch Date	Plan to Launch?	Review Date
1	Project A	1/1/2020	US	1/1/2021	1/1/2021	TRUE	1/1/2021
2	Project B	2/1/2020	US	2/1/2021	2/1/2021	TRUE	1/1/2021
3	Project C	3/1/2020	US	3/1/2021	3/1/2021	TRUE	1/1/2021
4	Project D	4/1/2020	US	4/1/2021	4/1/2021	TRUE	1/1/2021
5	Project E	5/1/2020	US	5/1/2021	5/1/2021	TRUE	1/1/2021
1	Project A	1/1/2020	Canada	1/1/2021	1/1/2021	FALSE	1/1/2021
2	Project B	2/1/2020	Canada	2/1/2021	2/1/2021	FALSE	1/1/2021
3	Project C	3/1/2020	Canada	3/1/2021	3/1/2021	FALSE	1/1/2021
4	Project D	4/1/2020	Canada	4/1/2021	4/1/2021	FALSE	1/1/2021
5	Project E	5/1/2020	Canada	5/1/2021	5/1/2021	FALSE	1/1/2021
1	Project A	1/1/2020	Mexico	1/1/2021	1/1/2021	TRUE	1/1/2021
2	Project B	2/1/2020	Mexico	2/1/2021	2/1/2021	TRUE	1/1/2021
3	Project C	3/1/2020	Mexico	3/1/2021	3/1/2021	TRUE	1/1/2021
4	Project D	4/1/2020	Mexico	4/1/2021	4/1/2021	FALSE	1/1/2021
5	Project E	5/1/2020	Mexico	5/1/2021	5/1/2021	FALSE	1/1/2021
1	Project A	1/1/2020	Israel	1/1/2021	1/1/2021	FALSE	1/1/2021
2	Project B	2/1/2020	Israel	2/1/2021	2/1/2021	FALSE	1/1/2021
3	Project C	3/1/2020	Israel	3/1/2021	3/1/2021	FALSE	1/1/2021
4	Project D	4/1/2020	Israel	4/1/2021	4/1/2021	TRUE	1/1/2021
5	Project E	5/1/2020	Israel	5/1/2021	5/1/2021	TRUE	1/1/2021

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

[HotChilli · Accepted solution]

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

[HotChilli]

@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

[Stev_data]

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

[SarahAnn]

@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.

[AlexisOlson · Accepted solution]

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 · Accepted solution]

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