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.
Hi I thought I had solved my issue, but I was mistaken. I have more attributes and some records have multiple values for each attribute. When I go to pivot the attribute column, the issue I'm having is that for one record with one value in each attribute, it's splitting out all the attributes seperately into individual attribute rows. What I need is for it to merge all the attributes for that single record, but create multiple rows for that record if they have say two values for one attribute, does that make sense? Below is my current query
let
Source = Excel.Workbook(File.Contents("C:\Users\30109290\OneDrive - Bright Horizons\Test Template.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Name", type text}, {"BHID", Int64.Type}, {"BHEmail", type text}, {"Role", type text}, {"Regular Finance Report Approver Name", type text}, {"Approver Email", type text}, {"% available for projects(rounded)", type any}, {"ADO Team Name 1", type text}, {"Proj.1 Allocation", type number}, {"PROJECT ASSET 1", type text}, {"PMO Project Name 1", type text}, {"Finance WD Project ID 1", Int64.Type}, {"ADO Team Name 2", type text}, {"Proj.2 Allocation", type any}, {"PROJECT ASSET 2", type text}, {"PMO Project Name 2", type text}, {"Finance WD Project ID 2", Int64.Type}, {"ADO Team Name 3", type text}, {"Proj.3 Allocation", type number}, {"PROJECT ASSET 3", type text}, {"PMO Project Name 3", type text}, {"Finance WD Project ID 3", Int64.Type}, {"ADO Team Name 4", type text}, {"Proj. 4 Allocation", type text}, {"PROJECT ASSET 4", type text}, {"PMO Project Name 4", type text}, {"Finance WD Project ID 4", Int64.Type}, {"ADO Team Name 5", type any}, {"Proj 5 alloc %", type any}, {"PROJECT ASSET 5", type any}, {"PMO Project Name 5", type any}, {"Finance WD Project ID 5", type any}, {"ADO Team Name 6", type any}, {"Proj. 6 Allocation", type any}, {"PROJECT ASSET 6", type any}, {"PMO Project Name 6", type any}, {"Finance WD Project ID 6", type any}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "BHID","BHEmail","Role","Regular Finance Report Approver Name","Approver Email","% 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", "BHID","BHEmail","Role","Regular Finance Report Approver Name","Approver Email","% available for projects(rounded)", "Value", "Index"}, {"Name", "BHID","BHEmail","Role","Regular Finance Report Approver Name","Approver Email","% 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"
Output I'm getting for a row in which there is one value for each of the attributes. The attributes are highlighted in green, all other columns are static
Output I want -