Forum Discussion
Solved
Create new rows based on the column data
Hi, I'm looking to see if I can take one of my tables that has data on each individual and all the teams they are assigned to and transform it so that for every team an individual is apart of, create...
Hi Bristix22
You can put the following code to advanced editor in power query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMS1XSUSouBRIlqcUlCiWpiblAtoGeGUwksbg4tQRZWqE8Ix+sxEQpVidaKSQfpCE3EUikZRalIkwwgonATChPLEktQsgbK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Role/Title" = _t, #"ADO Team Name 1" = _t, #"ProJ.1 Allocaiom" = _t, #"PROJECT ASSET" = _t, #"ADO Team Name 2#(lf)" = _t, #"ProJ.2 Allocaiom#(lf)" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Role/Title", type text}, {"ADO Team Name 1", type text}, {"ProJ.1 Allocaiom", type number}, {"PROJECT ASSET", type text}, {"ADO Team Name 2#(lf)", type text}, {"ProJ.2 Allocaiom#(lf)", type number}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Role/Title","PROJECT ASSET"}, "Attribute", "Value"), Custom1 = Table.TransformColumns(#"Unpivoted Other Columns",{"Attribute",each Text.Remove(_,{"0".."9","."})}), #"Cleaned Text" = Table.TransformColumns(Custom1,{{"Attribute", Text.Clean, type text}}), #"Grouped Rows" = Table.Group(#"Cleaned Text", {"Attribute"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1),type table}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Name", "Role/Title", "PROJECT ASSET", "Value", "Index"}, {"Name", "Role/Title", "PROJECT ASSET", "Value", "Index"}), #"Pivoted Column" = Table.Pivot(#"Expanded Count", List.Distinct(#"Expanded Count"[Attribute]), "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}) in #"Removed Columns"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Bristix22
You can put the following code to advanced editor in power query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMS1XSUSouBRIlqcUlCiWpiblAtoGeGUwksbg4tQRZWqE8Ix+sxEQpVidaKSQfpCE3EUikZRalIkwwgonATChPLEktQsgbK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Role/Title" = _t, #"ADO Team Name 1" = _t, #"ProJ.1 Allocaiom" = _t, #"PROJECT ASSET" = _t, #"ADO Team Name 2#(lf)" = _t, #"ProJ.2 Allocaiom#(lf)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Role/Title", type text}, {"ADO Team Name 1", type text}, {"ProJ.1 Allocaiom", type number}, {"PROJECT ASSET", type text}, {"ADO Team Name 2#(lf)", type text}, {"ProJ.2 Allocaiom#(lf)", type number}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Role/Title","PROJECT ASSET"}, "Attribute", "Value"),
Custom1 = Table.TransformColumns(#"Unpivoted Other Columns",{"Attribute",each Text.Remove(_,{"0".."9","."})}),
#"Cleaned Text" = Table.TransformColumns(Custom1,{{"Attribute", Text.Clean, type text}}),
#"Grouped Rows" = Table.Group(#"Cleaned Text", {"Attribute"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1),type table}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Name", "Role/Title", "PROJECT ASSET", "Value", "Index"}, {"Name", "Role/Title", "PROJECT ASSET", "Value", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Count", List.Distinct(#"Expanded Count"[Attribute]), "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
#"Removed Columns"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Edit****
I figured it out, I had to sort the columns by name prior to doing the indexing
Hi I have an issue with this solution, I'm 99% there, but there is an issue with my index lining up with my attributes.
I have two attributes - 'ADO Team Name and 'Proj Allocation'
The index number for these is not lining up correctly
Example - Original row
| Name | Role | ADO Team Name | Proj Allocation |
| Alex smith | Supervisor | ADO Team Name Example | 25% |
For this specific individual after applying the advanced query, I am getting two rows one with ADO Team Name value and one with the Proj Allocation percentage value - this is because the index numbers are diffierent for the two attributes (Ex: ADO Team Name attribute idex for this individual is 35, but the Proj Allocation attribute index number for this individual is 45)
My current Query -
let
Source = Excel.Workbook(File.Contents("C:\Users\30109290\OneDrive - Bright Horizons\capital_expense_test.xlsx"),null,true),
Table1 = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1,{{"Name", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Role","PROJECT ASSET","BUC or CC","% available for projects(rounded)"},"Attribute", "Value"),
Custom1 = Table.TransformColumns(#"Unpivoted Other Columns",{"Attribute",each Text.Remove(_,{"0".."9","."})}),
#"Cleaned Text" = Table.TransformColumns(Custom1,{{"Attribute", Text.Clean, type text}}),
#"Grouped Rows" = Table.Group(#"Cleaned Text", {"Attribute"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1),type table}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Name", "Role","PROJECT ASSET","BUC or CC","% available for projects(rounded)", "Value", "Index"}, {"Name", "Role","PROJECT ASSET","BUC or CC","% available for projects(rounded)", "Value", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Count", List.Distinct(#"Expanded Count"[Attribute]), "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
#"Removed Columns"