Forum Discussion
New Duplicate Row with New Value based on Condition
Dear Ibendlin,
I'm not sure I understand what you mean but either way, I wish to thank you for your time and help.
I came to the forum after having struggled and failed for some time, and it seemed that "giving up" was the break I needed as I managed to achieve my desired outcome.
I therefore also apologise if this post wasted your time.
The code in the Advanced editor that gave me the desired result is:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project_Name", type text}, {"Project_Ref", type text}, {"Project_Key", Int64.Type}, {"Project_Budget", type number}, {"Project_Hours", type number}, {"Invoice_Ref", type text}, {"Invoice_Date", type datetime}, {"Invoice_Total", type number}, {"Product_Key", Int64.Type}, {"Product_Name", type text}, {"Product_Parent", type text}, {"Product_Total", type number}, {"Companies", type text}, {"Sales", type text}, {"Coordinators", type text}, {"Speakers", type text}, {"Speaker_Keys", type text}, {"Speaker_Rate (max)", type number}, {"Speaker_KM", type number}, {"BonusCheck", type text}, {"Speaker_Product_Cost", type number}}), Custom1 = #"Changed Type", #"Added Custom" = Table.AddColumn(Custom1, "Custom", each if([Product_Name] = "Venue Cost") then List.Repeat({[Project_Name],[Project_Ref],[Project_Key],[Project_Budget],[Project_Hours],[Invoice_Ref],[Invoice_Date],[Invoice_Total],0,"Transport Cost","Parent transport costs",0,[Companies],[Sales],[Coordinators],[Speakers],[Speaker_Keys],[#"Speaker_Rate (max)"],[Speaker_KM],[BonusCheck],[Speaker_Product_Cost]},1) else List.Repeat({},0)), #"Custom2" = Table.Combine({#"Added Custom", Table.RenameColumns(Table.SelectRows(Table.Transpose(Table.FromColumns(#"Added Custom"[Custom])), each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),{{"Column1", "Project_Name"}, {"Column2", "Project_Ref"}, {"Column3", "Project_Key"}, {"Column4", "Project_Budget"}, {"Column5", "Project_Hours"}, {"Column6", "Invoice_Ref"}, {"Column7", "Invoice_Date"}, {"Column8", "Invoice_Total"}, {"Column9", "Product_Key"}, {"Column10", "Product_Name"}, {"Column11", "Product_Parent"}, {"Column12", "Product_Total"}, {"Column13", "Companies"}, {"Column14", "Sales"}, {"Column15", "Coordinators"}, {"Column16", "Speakers"}, {"Column17", "Speaker_Keys"}, {"Column18", "Speaker_Rate (max)"}, {"Column19", "Speaker_KM"}, {"Column20", "BonusCheck"}, {"Column21", "Speaker_Product_Cost"}})}) in Custom2And a picture of the outcome:
I've managed to create the desired row as a list in a custom column, but I am unable to convert this into a new row.
In the expand dialog there is an "Advanced Options" section. Open that and select "to rows"
Dear Ibendlin,
thank you for your suggestion. Unfortunately, unless I am misunderstanding you, that does not quite yield the desired result as it just expands the table downwards creating duplicate rows and filling in the data in the last column. Instead I want just a single new row with data in that cell in the last column.
The result I get is the following:
I know I can also extract the data in a single cell and use delimiter, but I still don't know how I would then convert that into a new row either.
Instead I want just a single new row with data in that cell in the last column.Once you have that, how do you plan to correlate that back to the rest of the data? Makes no sense to me ?
Dear Ibendlin,
I'm not sure I understand what you mean but either way, I wish to thank you for your time and help.
I came to the forum after having struggled and failed for some time, and it seemed that "giving up" was the break I needed as I managed to achieve my desired outcome.
I therefore also apologise if this post wasted your time.
The code in the Advanced editor that gave me the desired result is:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project_Name", type text}, {"Project_Ref", type text}, {"Project_Key", Int64.Type}, {"Project_Budget", type number}, {"Project_Hours", type number}, {"Invoice_Ref", type text}, {"Invoice_Date", type datetime}, {"Invoice_Total", type number}, {"Product_Key", Int64.Type}, {"Product_Name", type text}, {"Product_Parent", type text}, {"Product_Total", type number}, {"Companies", type text}, {"Sales", type text}, {"Coordinators", type text}, {"Speakers", type text}, {"Speaker_Keys", type text}, {"Speaker_Rate (max)", type number}, {"Speaker_KM", type number}, {"BonusCheck", type text}, {"Speaker_Product_Cost", type number}}), Custom1 = #"Changed Type", #"Added Custom" = Table.AddColumn(Custom1, "Custom", each if([Product_Name] = "Venue Cost") then List.Repeat({[Project_Name],[Project_Ref],[Project_Key],[Project_Budget],[Project_Hours],[Invoice_Ref],[Invoice_Date],[Invoice_Total],0,"Transport Cost","Parent transport costs",0,[Companies],[Sales],[Coordinators],[Speakers],[Speaker_Keys],[#"Speaker_Rate (max)"],[Speaker_KM],[BonusCheck],[Speaker_Product_Cost]},1) else List.Repeat({},0)), #"Custom2" = Table.Combine({#"Added Custom", Table.RenameColumns(Table.SelectRows(Table.Transpose(Table.FromColumns(#"Added Custom"[Custom])), each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),{{"Column1", "Project_Name"}, {"Column2", "Project_Ref"}, {"Column3", "Project_Key"}, {"Column4", "Project_Budget"}, {"Column5", "Project_Hours"}, {"Column6", "Invoice_Ref"}, {"Column7", "Invoice_Date"}, {"Column8", "Invoice_Total"}, {"Column9", "Product_Key"}, {"Column10", "Product_Name"}, {"Column11", "Product_Parent"}, {"Column12", "Product_Total"}, {"Column13", "Companies"}, {"Column14", "Sales"}, {"Column15", "Coordinators"}, {"Column16", "Speakers"}, {"Column17", "Speaker_Keys"}, {"Column18", "Speaker_Rate (max)"}, {"Column19", "Speaker_KM"}, {"Column20", "BonusCheck"}, {"Column21", "Speaker_Product_Cost"}})}) in Custom2And a picture of the outcome: