Forum Discussion
Pivot Unpivot Multiple values
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.
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 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"