Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SarahAnn
New Member

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!

2 ACCEPTED SOLUTIONS
HotChilli
Super User
Super User

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

 

View solution in original post

AlexisOlson
Super User
Super User

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:

AlexisOlson_0-1636673173781.png

 

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"

View solution in original post

5 REPLIES 5
HotChilli
Super User
Super User

@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
Frequent Visitor

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

Stev_data_1-1643735313175.png

 

 

 

SarahAnn
New Member

@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
Super User
Super User

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:

AlexisOlson_0-1636673173781.png

 

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
Super User
Super User

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.