New Duplicate Row with New Value based on Condition

[kd-blcc]

Hello,

Context: in the company's software we can export itemised bills to clients to Excel. To calculate the margin we get the speaker rate based on the item. For some clients the cost of the speaker's transport is not itemised. However, we can infer there is a transport cost due to the Venue Cost being an item in the bill, so an extra row can be created.

A similar issue occurs with bonuses. These are obviously not billed to the client but an extra row should be created if the conditions are met.

Current Situation: After trying to find a solution online, 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. I'm also not sure if this is the best way of doing things. So I am open to other methods. I have some programming experience but I wouldn't say I'm an expert; and I would like to understand the solution proposed.

Pictures for reference (all information is obviously placeholder but it is fairly true to the original):

Start Data:

Desired Outcome Transport & Bonus: 

OutcomeTransportOutcomeBonus

Custom Column Step (I would also like to know if there's an "else DoNothing()" option): 

CustomColumn_Step

Column List:

ColumnList

Advanced Editor:

AdvancedEditor

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],[Product_Key],"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))
in
#"Added Custom"

Thank you very much in advance.
Sincerely,

[lbendlin]

 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"

[kd-blcc]

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.

[lbendlin]

 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 ?

[kd-blcc · Accepted solution]

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
    Custom2

And a picture of the outcome: