Advanced string and number transformations to a power query table

Question

Hello all,

I need to split out specific rows that are labeled under the Line column "Project Wide" into two rows with halved quantities and changed Reference numbers from REF.#.L-PW to REF.#.L-49 and REF.#.L-50. The reindexing is simple enough for me to do. Here are some pictures for a before and after to better describe my desired outcome.

Before:

After:

I've been having trouble doing multiple data transformations and splitting a quantity without creating more than two rows per every one project wide row. The goal is to have a project wide entry automatically split into a 49 and 50 entry with halved quantities.

Answer 1

Hi @Isaiah Carleton

No [Quantity] in your 1st picture, I assumed this is a mistake. One way (I didn't re-create all your colums):

let  
    Source = Table.FromRecords(  
        {  
            [Line="Project wide",ID=1,Date_Submitted=#date(2020,11,14),Reference_Number="96.7.L-PW",Qty=20],  
            [Line="Project wide",ID=2,Date_Submitted=#date(2020,11,20),Reference_Number="96.1.L-PW",Qty=9],  
            [Line="Project wide",ID=3,Date_Submitted=#date(2020,11,30),Reference_Number="96.12.L-PW",Qty=10]  
        },  
        type table [Line=text, ID=Int64.Type, Date_Submitted=date, Reference_Number=text, Qty=number]  
    ),  
    Transforms = Table.TransformColumns(Source,  
        {  
            {"Qty", each _/2, type number},  
            {"Reference_Number", (ref)=>  
                List.Transform({"49","50"}, each Text.Replace(ref, "PW", _)),  
                type list  
            }  
        }  
    ),  
    RefListAsRows = Table.ExpandListColumn(Transforms, "Reference_Number")  
in  
    RefListAsRows  

If you have trouble adapting it let me know. Otherwise if this solves your problem please mark this reply as answer (can help others). Thanks & Nice day...

Answer 2

Wow thanks for coming up with a solution so quickly.