Forum Discussion
Solved
transpose list
I have 3 goals...each goal has versions. I have easily listed each goal in a Table that shows each version. What i would like is to have a different grouping: Current State: Goal 1 Version ...
Hi jlynch
Please refer to the modified file
let Source1 = Table.SelectColumns(Sheet4,{"Goal 1","Version1"}), #"Pivoted Column1" = Table.Pivot(Table.TransformColumnTypes(Source1, {{"Version1", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Source1, {{"Version1", type text}}, "en-US")[Version1]), "Version1", "Goal 1"), Source2 = Table.SelectColumns(Sheet4,{"Goal 2","Version2"}), #"Pivoted Column2" = Table.Pivot(Table.TransformColumnTypes(Source2, {{"Version2", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Source2, {{"Version2", type text}}, "en-US")[Version2]), "Version2", "Goal 2"), Source3 = Table.SelectColumns(Sheet4,{"Goal 3","Version3"}), #"Pivoted Column3" = Table.Pivot(Table.TransformColumnTypes(Source3, {{"Version3", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Source3, {{"Version3", type text}}, "en-US")[Version3]), "Version3", "Goal 3"), #"Appended Query1"= Table.Combine({#"Pivoted Column1", #"Pivoted Column2", #"Pivoted Column3" }), #"Renamed Columns" = Table.RenameColumns(#"Appended Query1",{{"1", "Version 1"}, {"2", "Version 2"}, {"3", "Version 3"}}) in #"Renamed Columns"Best Regards
Maggie
Hi jlynch
In Query Editor
Create a blank query, code in Advanced editor
let
Source1 = Sheet1,
#"Pivoted Column1" = Table.Pivot(Table.TransformColumnTypes(Source1, {{"Version", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Source1, {{"Version", type text}}, "en-US")[Version]), "Version", "Goal 1"),
Source2 = Sheet2,
#"Pivoted Column2" = Table.Pivot(Table.TransformColumnTypes(Source2, {{"Version", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Source2, {{"Version", type text}}, "en-US")[Version]), "Version", "Goal 2"),
Source3 = Sheet3,
#"Pivoted Column3" = Table.Pivot(Table.TransformColumnTypes(Source3, {{"Version", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Source3, {{"Version", type text}}, "en-US")[Version]), "Version", "Goal 3"),
#"Appended Query1"= Table.Combine({#"Pivoted Column1", #"Pivoted Column2", #"Pivoted Column3" }),
#"Renamed Columns" = Table.RenameColumns(#"Appended Query1",{{"1", "Version 1"}, {"2", "Version 2"}, {"3", "Version 3"}})
in
#"Renamed Columns"
Best Regards
Maggie
this is awesome but one point I'd like to clarify, goal 1,2 and 3 are all in the same table as well as the version. so would I use that table as sheet1,2 and 3 and keep the different sources?
The way i built the data, there is a new version when any one of the goals change so there is 1 version per set of goals, and it's all in one table.
Just making sure I understand the syntax here.
Hi jlynch
Please refer to the modified file
let Source1 = Table.SelectColumns(Sheet4,{"Goal 1","Version1"}), #"Pivoted Column1" = Table.Pivot(Table.TransformColumnTypes(Source1, {{"Version1", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Source1, {{"Version1", type text}}, "en-US")[Version1]), "Version1", "Goal 1"), Source2 = Table.SelectColumns(Sheet4,{"Goal 2","Version2"}), #"Pivoted Column2" = Table.Pivot(Table.TransformColumnTypes(Source2, {{"Version2", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Source2, {{"Version2", type text}}, "en-US")[Version2]), "Version2", "Goal 2"), Source3 = Table.SelectColumns(Sheet4,{"Goal 3","Version3"}), #"Pivoted Column3" = Table.Pivot(Table.TransformColumnTypes(Source3, {{"Version3", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Source3, {{"Version3", type text}}, "en-US")[Version3]), "Version3", "Goal 3"), #"Appended Query1"= Table.Combine({#"Pivoted Column1", #"Pivoted Column2", #"Pivoted Column3" }), #"Renamed Columns" = Table.RenameColumns(#"Appended Query1",{{"1", "Version 1"}, {"2", "Version 2"}, {"3", "Version 3"}}) in #"Renamed Columns"Best Regards
Maggie
does it matter if in table4 - Version1, version2 and Version3 are actually the same column? I assume I can just use that one variable in place of the 3 and therefor thank you for your solution.