Pivot Unpivot Multiple values

[radar]

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

Project Title

Project Manager

Risk Name 1

Risk Category 1

Risk Status 1

Risk Priority 1

Risk Name 2

Risk Category 2

Risk Status 2

Risk Priority 2

Risk Name 3

Risk Category 3

Risk Status 3

Risk Priority 3

Project1

PM1

Risk1

Category1

Status1

Priority1

Risk2

Category2

Status2

Priority2

Risk3

Categor3

Status3

Priority3

Project2

PM2

RiskP2

Category1

Status2

Priority3

Project3

PM3

RiskP3

Categor3

Status1

Priority2

RiskP32

Category1

Status3

Priority1

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

Project Title	Project Manager	Risk Name	Risk Category	Risk Status	Risk Priority
Project1	PM1	Risk1	Category1	Status1	Priority1
Project1	PM1	Risk2	Category2	Status2	Priority2
Project1	PM1	Risk3	Categor3	Status3	Priority3
Project2	PM2	RiskP2	Category1	Status2	Priority3
Project3	PM3	RiskP3	Categor3	Status1	Priority2
Project3	PM3	RiskP32	Category1	Status3	Priority1

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!

[HotChilli]

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

[radar]

Thank you!

[HotChilli · Accepted solution]

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.

[radar]

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]

Are you using sample data or the real data?

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

[radar]

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

Project Title

Project Manager

Total Risks

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

Project1

PM1

3

Risk1

High

Finance

Closed

Risk2

Medium

Schedule

Realized

Risk3

Very High

Scope

Cancelled

Project2

PM2

1

RiskP2

Low

Staffing

Open

Project3

PM3

2

RiskP3

Vey High

Scope

Realized

RiskP32

Low

Finance

Open

Project4

PM4

0

Here's the erorr I'm getting: 

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]

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

[radar]

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?