Create new rows based on the column data

[Bristix22]

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 a duplicate entry/row for that individual that shows the different team they are on. Ideally, if there is no data in the secondary columns (ADO Team Name 2) then it would not create a second row for that individual. 

ex: current table with unique individuals but with multiple teams in one row 

And transform it, so that there are now multiple rows for that individual if they are apart of multiple teams

Any ideas on how to go about solving this?

[v-xinruzhu-msft · Accepted solution]

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.

[Bristix22]

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 -

[Bristix22]

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"

[ryan_mayu]

maybe you can try in PQ reference two tables and appendix

pls see the attachment below