Subcategory in same column need to be split in powerquery

[s2310]

Customer and items sold in same column need to made into usable table format.
Please see attached excel file to understand the question.
Please help solve this.

 

[AlanSidman]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice No.", type text}, {"Sales", type text}, {"quantity", Int64.Type}, {"rate", type number}, {"Total", type number}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Invoice No."}),
    #"Added Custom" = Table.AddColumn(#"Filled Down", "Customer", each if Text.Contains([Sales],"Customer") then [Sales] else null),
    #"Filled Down1" = Table.FillDown(#"Added Custom",{"Customer"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down1",{"Invoice No.", "Customer", "Sales", "quantity", "rate", "Total"}),
    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each not Text.StartsWith([Sales], "Customer"))
in
    #"Filtered Rows"

 

[s2310]

Table.AddColumn(#"Filled Down", "Customer", each if Text.Contains([Sales],"Customer") then [Sales] else null),

Hello Mr.Alan,

Can you please tell me how to do this step? Is this a written formula or mouse button clicked step.
Please help.

Thankyou.

 

[AlanSidman]

click on Add Column and type the Mcode provided. It is an If-Then-Else statement. It is case sensitive.