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

Pivot Unpivot Multiple values

Hi friends - One of my data sources is a SharePoint list (created from SP forms) and is in the format below:

Project TitleProject ManagerRisk Name 1Risk Category 1Risk Status 1Risk Priority 1Risk Name 2Risk Category 2Risk Status 2Risk Priority 2Risk Name 3Risk Category 3Risk Status 3Risk Priority 3
Project1PM1Risk1Category1Status1Priority1Risk2Category2Status2Priority2Risk3Categor3Status3Priority3
Project2PM2RiskP2Category1Status2Priority3        
Project3PM3RiskP3Categor3Status1Priority2RiskP32Category1Status3Priority1    

 

I need to modify this list in to the format below, so I can append this dataset to other datasets:

Project Title Project ManagerRisk NameRisk CategoryRisk StatusRisk Priority
Project1PM1Risk1Category1Status1Priority1
Project1PM1Risk2Category2Status2Priority2
Project1PM1Risk3Categor3Status3Priority3
Project2PM2RiskP2Category1Status2Priority3
Project3PM3RiskP3Categor3Status1Priority2
Project3PM3RiskP32Category1Status3Priority1

 

How can I achieve this using Power Query or any other way in Power BI? I tried multiple ways (unpivoting, removing duplicates, pivoting) but not able to achieve desired output. Please help!

 

 

 

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

it's the #"Removed Columns" line.  The pivot needs that column to identify unique rows.

So remove that step.  You might want to filter out blank values from the Value column also.

Then do the pivot.

If you need to filter out rows after that then you can identify them at that stage.

View solution in original post

8 REPLIES 8
HotChilli
Super User
Super User

If you don't filter out blank values (my suggestion in previous post), then you should still see Project4 rows after pivot 

Thank you!

HotChilli
Super User
Super User

it's the #"Removed Columns" line.  The pivot needs that column to identify unique rows.

So remove that step.  You might want to filter out blank values from the Value column also.

Then do the pivot.

If you need to filter out rows after that then you can identify them at that stage.

Life Saver! That worked! Thank you so much! One small issue is there is Porject4 which has 0 risks and is getting removed automatically when I unpivot, how can I keep the projects that have 0 risks (risk information is not populated for those)?

HotChilli
Super User
Super User

Are you using sample data or the real data?

Post the 'Advanced Editor' code and I'll have a look

Thank you so much taking time to help me. I have modified the input file to reflect more acccurate situation: 

Project TitleProject ManagerTotal RisksRisk Name 1Risk Priority 1Risk Impact 1Risk Status 1Risk Name 2Risk Priority 2Risk Impact 2Risk Status 2Risk Name 3Risk Priority 3Risk Impact 3Risk Status 3
Project1PM13Risk1HighFinanceClosedRisk2MediumScheduleRealizedRisk3Very HighScopeCancelled
Project2PM21RiskP2LowStaffingOpen        
Project3PM32RiskP3Vey HighScopeRealizedRiskP32LowFinanceOpen    
Project4PM40            

 

Here's the erorr I'm getting: 

radar_0-1669049478663.png

 

Here's the M code:

let
Source = Excel.Workbook(File.Contents("C:\Users\radarla\Documents\Risks.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project Title", type text}, {"Project Manager", type text}, {"Total Risks", Int64.Type}, {"Risk Name 1", type text}, {"Risk Priority 1", type text}, {"Risk Impact 1", type text}, {"Risk Status 1", type text}, {"Risk Name 2", type text}, {"Risk Priority 2", type text}, {"Risk Impact 2", type text}, {"Risk Status 2", type text}, {"Risk Name 3", type text}, {"Risk Priority 3", type text}, {"Risk Impact 3", type text}, {"Risk Status 3", type text}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Risk Name 1", "Risk Priority 1", "Risk Impact 1", "Risk Status 1", "Risk Name 2", "Risk Priority 2", "Risk Impact 2", "Risk Status 2", "Risk Name 3", "Risk Priority 3", "Risk Impact 3", "Risk Status 3"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Only Selected Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute.1]), "Attribute.1", "Value")
in
#"Pivoted Column"

 

HotChilli
Super User
Super User

There are definitely examples of this in this forum.

The basic algorithm is :

Select the first two columns->Unpivot other columns

Split the Attribute Column to make 2 columns. The left one (call it C1) will be a limited set of values which will be the new column headings.

Pivot the C1 column using Values column in Values and "Don't Aggregate" in the Advanced Section.

For sample data as provided that will work.  The real data may not have numbers after the words so may be a little more complex.

--

Good luck

Thank you for replying. I tried all those steps, when I try to pivot as the last step I'm getting Expression Error: There were too many elements in th enumeration to complete the operation. Details: [List]. 

Which I understand as there are multiple values for the same field which should be treated as a duplicate (for all other values except the currrent one) record as shown in the example above and create a new row, but that's not happening. Any suggestions on how to work around this error?

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.

Top Solution Authors
Top Kudoed Authors